Import CSV file

[gg80]

I am using A2007 and 2003. I would like to import a csv file. I don't want to use an import specification name because it is sensitive to column order In this application, the column order in the csv file can change. I found this simple code: on several sites.

DoCmd.TransferText TransferType:=acImportDelim, TableName:="MyTableName", _
FileName:=myPath & "MyCsvFile.csv", HasFieldNames:=True



When I run it, I get message error 3209 "the search key was not found in any record"

If I change acImportDelim to acLinkDelim, the code works fine and links to the csv file. I get rid of the column order problem, but get a new one in that Access assigns a format based on the first few items in a column. If the first few items are numbers and further down the column, there is an item with text and numbers, it rejects the item.

Is there some way to import csv to an access table without using a spec? Any help much appreciated.

[orange]

It's difficult to answer because the question is a little vague. Normally if you design a system that is dependent on an external csv file, you would agree with the supplier of the csv file what will be the format, the field order, the data types etc.
It seems you have a situation where the incoming file has no specific format.

[ItsMe]

It might seem OK to have a column in a table of data type Integer for fields that are going to hold numbers that represent apartment numbers. After all, most apartment numbers are in a range somewhere between 1 and 10,000. It should fit into an integer. That is until one day someone tries to input D-10. So really, that column should be text and maybe limited to 10 or 15 characters.

[gg80]

I'm not sure what you mean by "specific format" in a csv file. My understanding is that a csv file is all text format. It is Access that bollixes things up on import when it insists that if the first item in column A is a number then all things in column A must be number format. Then if an item is a combined text and number, it will show that particular item as an error in format and not display it in the linked table. The CSV file is all text, why does Access force a change?

If you can give me a clue as to why the code doesn't work with acImportDelim so that I can import a csv file instead of linking it, I can take it from there. Puzzling because I have seen this code posted in several places.

Thanks for quick reply.

[ItsMe]

You can receive text and place it into a field of a different data type. Sometimes the data is truncated and other times there is a fatal exception. The preference is for it to work perfectly and have the text fit into the data type. One example is a date in text format fitting into a Date field. 1/1/1990 in text will fit into a date field

[gg80]

Hello ItsMe-thanks for reply and for help on other items. How do I or force a certain column in csv to be formatted as text when linked to Access. Isn't a CSV file already just delimited text? If I could import it to a table to an Access table instead of linking, I could force the field in the Access table to be text format. I would have some control.

Do you see some reason why the code to do an import doesn't work?

[ItsMe]

When I have goofy data to import, I import it as text and then analyze it, finalizing the process by bringing it into a permanent table with the correct data types.

Like Orange mentioned there are issues with having a CSV file's data structure changing. If it changes from one day top another, use text data types for your table's fields. If the data type changes from one row to another, use text data types for your table's fields.

Orange may show you how to read a text file and analyze it line by line. I have not tried that yet.

[gg80]

I'm afraid you two have me really confused. It is hard for me to understand how a csv file can be "formatted, structured, have a specific format, etc". A csv file is just text with text expressions separated with commas. Access auto formats fthe file when the code performs the link. In any case, if I could import to a table, I could do the things you describe.

My basic problem is that the code I posted won't import to a table. Can you or Orange show me code that will import a csv file to an access table. (NOT code that uses a specification name.)

[ItsMe]

How I import text files, delimited or fixed width, is to use the wizard and create, name, and save the import procedure. I have never had a problem doing it this way. Once the procedure is created, you can not edit the procedure but you can edit the table that you import to. In fact, you do not need to even use the same table that was associated with the wizard.

The other approach uses a lot of code to create an object and interface with the text files. I have done this to print to text files but never bothered to read text files using VBA. This is why I suggested in the other thread to copy the CSV and paste it as XLSX. It would probably be easier to interact with an Excel file using VBA and you would probably have better control over formatting. You could possibly change the CSV to XSLX, open as an Excel object and analyze the data using the Excel object; bringing over data directly to a permanent table.

I still recommend using the wizard and importing it into a table. Once it is in the temp table you can use basic validation techniques to verify data type and size, Header Names, etc.

If the data structure is always changing, Import the data into a temp table where each column is TEXT. Then bring it into another (permanent) table via a data validation process.