importing xls, csv and txt

[ceelly]

Hi,

We get data in from others and they can supply this data in specifed format that we manipulate in excel before importing the data onto the database. (don't worry we use code to do the manipulation we don't do most of it mannually).. we specified the arrangement of data in the file; the headers and the information in each col.. eg the sample date of the sample, the parameter measured, the value of the paramter ect.

Just putting this into practice and we were planning this would be provided to us in csv format but it has arrived in an excel format..

I am just curious.. has anyone out there written or know of code that will import either a xls, csv or txt file into a excel spreadsheet ( which contains our code for manipulating the data before importing it into the database)...

thanking in advance...

Ceelly

 

[ceelly]

arr.. no replies.. that is OK.. I ended up simply writing some code to get the extension of the file then using the good old if else statements to import the file depending the the extension.. I was wondering if there was something a bit more eligant way of doing this because it is rather ugly and involves a far bit of code.

but I guess not..

 

[Ruddles]

You asked your question whilst I was fast asleep and gave up waiting for an answer before I woke up this morning. Patience is a virtue!

If I had to write a single bit of code to process a file which might be XLS or it might be CSV but I didn't know beforehand, I would probably just Workbooks.Open it and the read the data from the worksheet. If it was a TXT file, I would detect that beforehand and import/parse it, then use the same code to read the data from the worksheet as I used for the XLS/CSV file - then that bit of code at least would not need to be replicated for each file type.

 

[ceelly]

thanks Ruddles.. not impatience just needed to get the job done.. I did a similar way that you suggested.. I used If then else to detect if the import file was a text file and I also used the Workbooks.Open to read the excel spread sheet.. after reading in the file ( text, csv or excel). I used the same piece of code at the end of the program to preform the data manipulation..

Would be nice if there was a line that would read in all files types.. oh well..

Thanks for your reply.. it is nice to know that I have the same approach as other people..