Import comma Delimited xlsx file that has NO Headers

[chip0105]

I have an issue that I hope is child's play for someone with more experience coding than I have.

I have an Assets.xlsx file that is actually a comma delimited file; all the data is contained within the A1 cell.

I need to be able to append the input file's data into a table that I have defined the fields as Field01, Field02, Field03, ...through Field10.

The fifth field looks to be a number field that has 4 decimal places. However, the field starts with a "+" sign which may qualify the data as a text field instead. The remainder of the delimited fields are text.

Below is an example of the data as it is in cell A1 of the spreadsheet:

00638909232,"CB FOR WASATCH EM SM CAP CIT ","B018BZ4 ","MINOR INTERNATIONAL PCL- /THB/",+0000000920000.0000,"THB ","USD "

Can someone provide code that would allow me to either link the data and append it into my existing table or import the data from the file directly into the existing table?

Any assistance would be GREATLY appreciated!!

Thanks in advance for your time and efforts.

Chip

 

[The_Doc_Man]

The typical approach for CSV file importation is either:

a) Define the table and fields with correct data types ahead of time then import the CSV file to that table directly. This is easiest for the one-off cases because if something goes wrong, you can just start over again. But if you are accumulating data on a regular basis, this has no margin for error after the first import succeeds - because you would garble your table if there is a second import that fails.

b) Import the spreadsheet to a temporary table and then use a second query that includes appropriate conversion functions such as CLng, CDate, CStr to force the correct values. This works best only if you are doing this on a one-time (or not-too-many-times) basis because of database bloat issues involving intermediate or "staging" tables.

c) Link the external file and use a query that performs the conversion functions. This avoids the issue of database bloat but you would still be susceptible to issues if you are doing this often and sometimes it fails. There are ways to prevent this from whacking your table, but we need to explore other factors first.

The import query for methods (b) or (c) might resemble the following (using your 7-field example as a data source)

INSERT INTO mytable (a, b, c, d, e, f, g ) SELECT CLng(Field01), Field02, Field03, Field04, CDbl(Field5), Field6, Field7 FROM tblCSV ;"

You would have to perhaps manipulate the links programmatically, so there is more to it than this. I have not touched on that solution because something else bothers me.

The question I have is what you mean by "all the data is in the A1 cell. If there are commas in the file and yet everything is somehow crammed into a single cell, then something is dreadfully wrong. We need to know exactly what you meant by the above and perhaps how it got that way.

 

[theDBguy]

Hi Chip. I imagine you tried to use the Import Wizard and it wanted to put all the data from cell A1 into one field called A1, correct? If so, have you tried renaming your .xlsx file into a .csv file and then try using the Import Wizard again? Just curious if it will work that way...


PS. Please remember, if you try it, make sure to use Import Text instead of Import Excel.

 

[Frothingslosh]

In addition to what the others posted above, with all the data in a single cell in Excel, how do you determine the end of the record? Does the spreadsheet only contain one record at a time, or does the cell include carriage returns inside it?

One thing you need to watch out for is this: cells in Excel can only contain 32k characters (lower for old versions). If you are getting a true csv file all dumped into a single cell in a spreadsheet, then you very much risk losing data.

 

[chip0105]

The typical approach for CSV file importation is either:

a) Define the table and fields with correct data types ahead of time then import the CSV file to that table directly. This is easiest for the one-off cases because if something goes wrong, you can just start over again. But if you are accumulating data on a regular basis, this has no margin for error after the first import succeeds - because you would garble your table if there is a second import that fails.

b) Import the spreadsheet to a temporary table and then use a second query that includes appropriate conversion functions such as CLng, CDate, CStr to force the correct values. This works best only if you are doing this on a one-time (or not-too-many-times) basis because of database bloat issues involving intermediate or "staging" tables.

c) Link the external file and use a query that performs the conversion functions. This avoids the issue of database bloat but you would still be susceptible to issues if you are doing this often and sometimes it fails. There are ways to prevent this from whacking your table, but we need to explore other factors first.

The import query for methods (b) or (c) might resemble the following (using your 7-field example as a data source)

INSERT INTO mytable (a, b, c, d, e, f, g ) SELECT CLng(Field01), Field02, Field03, Field04, CDbl(Field5), Field6, Field7 FROM tblCSV ;"

You would have to perhaps manipulate the links programmatically, so there is more to it than this. I have not touched on that solution because something else bothers me.

The question I have is what you mean by "all the data is in the A1 cell. If there are commas in the file and yet everything is somehow crammed into a single cell, then something is dreadfully wrong. We need to know exactly what you meant by the above and perhaps how it got that way.

I apologize for any confusion. When I indicated the data was all in the A1 cell, what I meant was all of the data for the first record was in the A1 cell, just like a text or CSU file would display it when working with a comma delimited file. There are 59 individual records in this file so the data rows would be 1 through 59.

 

[CJ_London]

I have an Assets.xlsx file that is actually a comma delimited file; all the data is contained within the A1 cell

to be clear, a .xlsx file is an excel file, not a .csv - if it was it would be a .csv file. However MS in their wisdom have defaulted the app to open .csv's as Excel - which is fine providing you don't save it as Excel will make its own interpretation of the data. The fact the data when viewed in Excel is all in column A implies it has already changed it or there is a 'non csv valid' character in there somewhere - probably at the end of the line and probable a whitespace character (chr(160)).

Better to always open .csv (or .txt) in notepad or notepad++. Or if opening in Excel, make sure you open read only.

In your data +0000000920000.0000 will probably not be interpreted correctly (the + will throw it out) and 00638909232 will probably be interpreted as 638909232.

If it is an excel file, then chances are it will be treated as a single field, so you will need to use the split function to separate into the individual elements. Saving is as an .csv file from excel is too late. However you could open excel, copy column A, paste to notepad and save as a .csv - should work