0

I was shocked when I learned that importing the excel data to sql database using OPENROWSET has downsides as it truncates the cells' values of it to 255-length-characters before it passes to the database. I'm now thinking of using xp_cmdshell to read the excel file's data and transfer it to database. however I'm clueless on how I could do that. could somebody help me to achieve that?

aj go
  • 637
  • 2
  • 10
  • 27
  • You might want to look into `BCP`, this link would help - https://learn.microsoft.com/en-us/sql/relational-databases/import-export/use-a-format-file-to-bulk-import-data-sql-server – Abhishek Feb 16 '18 at 13:16
  • Lots of good content in this discussion: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/18b7ad7d-48dc-4f3b-bdab-216f461b684a/ssis-wizard-cannot-import-text-columns-longer-then-255-using-excel-source?forum=sqlintegrationservices – JohnLBevan Feb 16 '18 at 13:18
  • Hi @Abhishek - I tried to search my query on the link you provided but failed to find that suites to my need. I also viewed JohnLBevan's provided link and none of it gives the answer to my problem. – aj go Feb 16 '18 at 14:59
  • @ajgo - if you are looking for importing data from file to `sql server` table(s) then one option is `BCP`. `xp_cmdshell` will only spawn a windows command shell and pass in a string for execution in your case which can be a `BCP` command. – Abhishek Feb 16 '18 at 15:39
  • @Abhishek - can I use BCP on importing the data from .xlsx file to sql server table? – aj go Feb 16 '18 at 18:28
  • @ajgo - definitely you can, for more details see answer section – Abhishek Feb 17 '18 at 13:27

1 Answers1

0

Yes BCP could be used to import data from excel(.xlsx) files into Sql Server tables. Only thing to remember here is from MS documentation -

Prerequisite - Save Excel data as text To use the rest of the methods described on this page - the BULK INSERT statement, the BCP tool, or Azure Data Factory - first you have to export your Excel data to a text file.

In Excel, select File | Save As and then select Text (Tab delimited) (.txt) or CSV (Comma delimited) (.csv) as the destination file type.

A sample BCP command to import data from a excel file (converted to tab delimited) into Sql Server table -

bcp.exe "MyDB_Copy.dbo.Product" in "C:\Users\abhishek\Documents\BCPSample.txt" -c -t"\t" -r"\n" -S ABHISHEK-HP -T -h TABLOCK

Read more about BCP and import here.

Abhishek
  • 2,482
  • 1
  • 21
  • 32
  • I tried using this approach but it always imports 0 rows. I use this synyax: bcp "DbName.dbo.TableName" in "C:\MyFolder\MyExcelFile.xlsx" -S MyServer\loginname -T.. – aj go Feb 19 '18 at 18:46