Is it possible to format Excel/CSV file into fmt/XML file via BCP

Question

Is it possible to format Excel/CSV file into fmt/XML file via BCP

lindsaywang

SSCrazy

Points: 2569
More actions
April 6, 2011 at 4:27 am

#248487

Hello,
I'm stuck at the issue that format the excel file to fmt/XML file via BCP.
I have to import lots of EXCEL files data into the table in the Database. openquery doesn't work on my machine, I'm thinking that we can use bulk insert to load data into the table in the database. But I have to create the table before I use bulk insert. I did some searchs on the internet, seems that I can use following way to load the data into table dynamic, and don't need to care the excel column struncture. But I don't know how to generate the .fmt file... is there anybody can share some info to format the excel file to .fmt file? Really appreciated if you can share anything with me. Thanks a lot.
SELECT * into FROM OPENROWSET (BULK '\\localhot\TestExcel\FiscalCSV.csv', FORMATFILE = '\\localhot\TestExcel\FiscalCSV.Fmt') AS Z
Thanks
Lindsay

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

Just to clarify, a csv file is not the same as Excel file as noted by the xls and xlsx extensions. CSV stands for Comma-Separated Value and is nothing more than a plain-text file. When Excel is installed it creates an association in Windows so when you double-click a file with a .csv extension the file opens in Excel...but you do not need Excel to create csv files.

Back to your issue, you'll need to create a format file (also nothing more than a text file but saved with a .fmt extension) that describes your csv file. Here is a good article to get you started: http://msdn.microsoft.com/en-us/library/ms191516.aspx

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

lindsaywang SSCrazy Points: 2569 More actions · Answer 2

Thanks for your response opc.three:)

The link is useful, but I saw it only have the sample to fromat a table/view in the database into the .fmt file, is there any way to format a Excel/CSV file into a .fmt file?

Thanks

Lindsay

lindsaywang SSCrazy Points: 2569 More actions · Answer 3

I have found another to create the table for excel. This thread can be closed:)

Thanks

Ling

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

Great, I am happy you found a solution. Would you mind posting what you did so others who find this post from a search engine can benefit as well?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

lindsaywang SSCrazy Points: 2569 More actions · Answer 5

Sure

I only use the openrowset (the query is something like below) to create the table for excel.

Thanks

Lindsay

SELECT * INTO Test.dbo.sheet2 FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=\\localhost\TestExcel.xlsx', [Sheet1$])

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 6

Your original post references a file with a .csv extension...did you switch to using a real Excel Workbook file?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

lindsaywang SSCrazy Points: 2569 More actions · Answer 7

Basically, there is no CSV file, my original file is Excel. Bacause BCP doesn't work on Excel, I convert my Excel file to CSV. Now I found the Excel can be read by openrowset directly, so this solution works for me:)

Thanks

Lindsay

shelir SSC Enthusiast Points: 106 More actions · Answer 8

I have the same problem importing data to my table on SQL Azure server. My data are in an excel file and cannot use BCP utility to import them to my table. I tried to use OPENROWSET but I get this error "'OPENROWSET' rowset provider not supported in this version of SQL Server."

Now my challenge is creating .fmt since my data is not in the tables.

Or There might another way of importing bulk data to my tables in SQL Azure

Please let me know if you have any suggestions

Thank you

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 9

You can use SSIS or the Import/Export Wizard (based on SSIS) from within SSMS.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

shelir SSC Enthusiast Points: 106 More actions · Answer 10

Thank you for your reply.

I have windows 10 and i could only download ssms express and it doesn't have import under task.

I downloaded SSDT for Visual Studio 10, when I open the Data Tool Operations it just trying to read the data structure after that it doesn't give me any options to import data.

What would be the steps to use ssdt from visual studio 10.

From Command line is there any specific script to run. because Bulk insert and bcp doesn't work as I explained before that my data is in a excel sheet

Thank you

Shelir

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 11

SSMS Express does contain the Import/Export Wizard. Try right-clicking on a Database, then Tasks, then Import Data...

The only limitation with SSMS Express is you cannot save the resulting SSIS Package dynamically generated by the Wizard.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

shelir SSC Enthusiast Points: 106 More actions · Answer 12

Thank you for your reply.

Unfortunately I didn't get the Import/Export option from the task list, may be is because I have Windows 10 installed.

But alternatively I installed Import / Export wizard and used it to import my excel data to the database.

Warm Regards

Shelir

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 13

Good deal. Happy you got something working. For others that might run across this thread, could you share the link to the installer you found that eventually let you run the Wizard?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

shelir SSC Enthusiast Points: 106 More actions · Answer 14

I installed both Import and Export Data Wizard (64 bit) and Import and Export Data Wizard (32 bit). Since Access and Excel have only 32 bit version provider the 32 bit wizard worked for me

Here is a link to learn more: https://msdn.microsoft.com/en-us/library/ms141209.aspx

Thank you