importing csv to a table

[McDimwitty]

I need to import a csv file with a file extension of XLS to an Access 2003 table programmatically.

How would I do that?

Mr McDimwitty

 

[qdogfball]

You can import a CSV file, but why would a csv file have an xls extension?

Wouldn't it then be an Excel spreadsheet?

The DoCmd.TransferText option is what you use to import or export text files from/into Access.

In a command button I setup:

DoCmd.TransferText acImportDelim, , "Invoices", "C:\Documents and Settings\Myname\My Documents\sampleCSVimport.csv", True

Do a search in the VB code after you create a button on a form.

Alternately, you can create a macro that does this.

 

[McDimwitty]

I am doing this for work, so I have absolutely no control over how the file I receive is formatted. I bet a lot of the people on here know how that is. The file my application gets is a text file which is delimited by semicolons, but it has a file extension of XLS.

My Access VBA application will allow users to import this kind of file daily in an existing table. Then my app will do some "magic" with the data.

Would Access treat a delimited text file as a delimited file with the .XLS extension? Would I have to define the columns / fields if I am importing this delimited file if it is being imported to an existing table?

=-= Mr. McDimwitty =-=

You can import a CSV file, but why would a csv file have an xls extension?

Wouldn't it then be an Excel spreadsheet?

The DoCmd.TransferText option is what you use to import or export text files from/into Access.

In a command button I setup:

DoCmd.TransferText acImportDelim, , "Invoices", "C:\Documents and Settings\Myname\My Documents\sampleCSVimport.csv", True

Do a search in the VB code after you create a button on a form.

Alternately, you can create a macro that does this.

 

[lmazur]

dynamic .csv file

Hello gentlemen,

is it possible to use in DoCmd.TransferText command not with fixed link to csv file, but to have some dynamic link? e.g. to have window where I can select the file from local path.
In fact I need to import csv file into access table, but user has to choose which file to import.
Do you have an idea how to do this?
thank you very much.

Lubos

 

[qdogfball]

I am doing this for work, so I have absolutely no control over how the file I receive is formatted. I bet a lot of the people on here know how that is. The file my application gets is a text file which is delimited by semicolons, but it has a file extension of XLS.

My Access VBA application will allow users to import this kind of file daily in an existing table. Then my app will do some "magic" with the data.

Would Access treat a delimited text file as a delimited file with the .XLS extension? Would I have to define the columns / fields if I am importing this delimited file if it is being imported to an existing table?

=-= Mr. McDimwitty =-=

My computer will not let me import a text file that looks like an Excel file.

"Run time Error 31519 You cannot import this file."

******
Sometimes, helping people rethink their lives will make yours better!

Maybe your company needs to rethink the whole strategy of why you are importing data?

I ran into this at my present company. A project exports data to Excel from Access. Then it gets put back into Access! ???? WHY bother going out of and then into! Converting data has been known to put in extra information, making the data useless.

Have them change the name. Microsoft has a naming convention that for files with .XLS it is an Excel spreadsheet. .CSV is a delimited file.

If you get hit by a bus, who will know that the .XLS is NOT an .XLS?

If a consultant/new hire comes in to take over, they will get blindsided by your company that does not follow the rest of the world!

Will the company not bother with Daylight Savings time? Just use whatever time they want, when they want?

Naming conventions can be a pain, but they are there for a reason!

 

[qdogfball]

Hello gentlemen,

is it possible to use in DoCmd.TransferText command not with fixed link to csv file, but to have some dynamic link? e.g. to have window where I can select the file from local path.
In fact I need to import csv file into access table, but user has to choose which file to import.
Do you have an idea how to do this?
thank you very much.

Lubos

Lubos,

If you want to dynamically name the file to be imported, I suggest that you create your own topic with that title "Dynamically named CSV imported into Access" or something like that.