Getting External Data
 

Dear All

Not sure if this is strickly a programming question as the VBA seems to function correctly, however it does get external data and that is where my problem is. Using Excel 2003, I need to import data from a CSV file which changes on a regular basis. I used the 'Get External Data' function and got the query working fine. This continued to work fine for over a year but then, for no apparent reason, the CSV file is truncated

The lengths of the lines in the CSV file change every time it is updated, sometimes their may be 120 cells of data sometimes only 20 and anything in between. Like I said this all worked fine, now however, only part of my data is improted (up to column AH), the rest does not make it into Excel although it is clearly in the CSV file. So I re-created the query and it fixed the problem, for a week! Exactly the same problem has now occured again, this time the data only fills to column AZ, so a slight variation

I am about to fix the problem again, again by recreating the query. I would really like to get the the route of this problem as although work-roundable, it is extreamly annoying and somewhat bizzarre

Should I re-install Excel

Thanks for taking the time to read my waffle

Matthew

Getting External Data
 

Try using the regular Excel File Open statement, like the following:
Workbooks.Open Filename:="C:\My Documents\Test Data CSV.csv"

Excel should understand CSV files and be able to open them using the regular
Open method (Text Files file type, if you are using the File Open dialog box
from the user interface). You might start up Explorer, choose the Folder
Options command from the Tools menu, and check to see what it has listed for
the CSV file type. The icon for the CSV extension type should be an Excel
icon (smaller "X" in it), and the File Type should be "Micosoft Excel Comma
Separated Values File".

You may have to browse some of your source data files with a programmer's
text file editor.
1. Are there funny characters part way across the line (something that would
make Excel think it has reached the end of a line)?
2. Remember that text strings must be enclosed in double quotes.

Also, you might re-read the "File format converters supplied with Microsoft
Excel" topic in Excel Help. Do you have all of the file converters
installed?

I wouldn't think that you would need to be using Get External Data to input
CSV files.
I don't think you need to re-install Excel 2003 (that would be a lot of
work!).

(I can open CSV files with 90 columns of data in Excel 2000, SP-3.)
--
Regards,
Bill


"Matthew" wrote in message
...
Dear All,

Not sure if this is strickly a programming question as the VBA seems to
function correctly, however it does get external data and that is where my
problem is. Using Excel 2003, I need to import data from a CSV file which
changes on a regular basis. I used the 'Get External Data' function and got
the query working fine. This continued to work fine for over a year but
then, for no apparent reason, the CSV file is truncated.

The lengths of the lines in the CSV file change every time it is updated,
sometimes their may be 120 cells of data sometimes only 20 and anything in
between. Like I said this all worked fine, now however, only part of my
data is improted (up to column AH), the rest does not make it into Excel
although it is clearly in the CSV file. So I re-created the query and it
fixed the problem, for a week! Exactly the same problem has now occured
again, this time the data only fills to column AZ, so a slight variation!

I am about to fix the problem again, again by recreating the query. I
would really like to get the the route of this problem as although
work-roundable, it is extreamly annoying and somewhat bizzarre!

Should I re-install Excel?

Thanks for taking the time to read my waffle,

Matthew

Getting External Data
 

< 2. Remember that text strings must be enclosed in double quotes.

I think text strings only need to be in quotes if they have a comma in the
middle of any string that will be imported into a single cell.

--
Regards,
Bill