Save csv file as excel

[TimTDP]

I need to import data from csv files into Access.

The problem that I have is that the format of the cvs file can change. Some have 5 columns od data, some have 6. Also the contents of the first row change!

I can handle the changes in the data if the data is available in an Excel spreadsheet.

How can I save a .csv file as an Excel file using vba (DAO)?

Or do I need to open the .csv file and import the data row for row? If yes, how do I do this?

Thanks in advance

 

[Galaxiom]

You can automate Excel from Access using VBA. The Excel application can be run hidden so the whole provess would be invisible.

However the import specification system in Access is quite similar to Excel so you should be able to use it. Specifications can be saved and reused for coded imports.

You could interact with the user to ask them to select a specification if such a thing was easily determined by the user. Another way is to use a file naming structure to indicate the specification to be used.

Text files can also be imported using a schema.ini file which specifies the structure of the database table to be extracted from the csv.

I prefer to import text files using the TextStreamObject sporting my own custom importing code that can also strip lines according to stored specification.

How you do it depends largely on how much variation you expect to encounter in the csv structure. It is possible to write code that recognise a patterns in the csv data and extract it accordingly. It really comes down to your ability to represent the pattern concepts in code.