Storing data from excel file to database and vice-versa

[Guest_imported]

Hello!

In my new project I need to store some data from an excel file to database and then store some other data from database to excel file using cold fusion. Is this possible?
Help.

Thanks,
sra1.

 

[GunJack]

If you can find some custom tags that can read/write to Excel files, I think this would be your best bet. There is an Excel ODBC driver which will allow you to connect to an Excel file but I've always heard this is problematic and not a very reliable method. I would search the Allaire tag gallery first and if you don't find anything there, I'd resort to the trying the ODBC method.

Good luck,
GJ

 

[Guest_imported]

Thanks for the suggestion. Do you think I can use CFFILE tag as we use for any other file to write to excel file and then to read from excel file and then use sql loader or something to store values into database?

Thanks,
Sra1

 

[GunJack]

You can use <cffile> to read and write to it but the problem is in understanding the format of the Excel file. If you're just reading and writing to a comma delimited text file, that is easy. If you're trying to add data to an Excel file, you need to have knowledge about how it stores it's field names, where it places data, etc... You might have luck doing this on a very simple spreadsheet but I think you run the risk of writing the data out in a format that causes problems if it doesn't follow the Excel file format exactly.

You could always try it and see but the real trick is figuring out and following the internal format of the Excel spreadsheet.

GJ

 

[Guest_imported]

Gunjack - Thanks ... I'll try it.
I noticed that on Mar28, w11z asked similar question. I am kinda curious w11z to know if you could successfully implement it?

Thanks,
sra1.

 

[w11z]

vintha,

I haven't tried it yet but i'm working on it next week. If a succeed, i'll let you know.

w11z

 

[dlkraft]

Using the ODBC driver has worked well for me when reading from and writing to an Excel file.

To use it, identify and name a range in your spreadsheet. The named range will be treated like a table. Identify your columns with names in the top row. These act as field names in your queries.

After the range is named in Excel, set up the ODBC connection.

In your query, syntax should be:

Select (fieldnames sep by comma) from (rangename)

Substitute your rangename above.

Good luck!