3

I would like load the huge excel and csv files data into SQL server dynamically based on configuration in database table. Each file will be different from another and it should load into table dynamically, Please provide the best way to achieve it?

I have tried with ssis but for excel ssis will determine the column datatype based on first 8 rows, Because of that it causing issue.

Hadi
  • 31,125
  • 9
  • 49
  • 111
Chandra Sekhar
  • 33
  • 3
  • Off-topic for SO - far too broad. Review the topic [what topics can i ask](https://stackoverflow.com/help/on-topic) – SMor Jun 27 '19 at 13:01
  • *You* control the number of rows used for inference. If you don't like the result, change the types. No application is going to guess the correct types all the time. – Panagiotis Kanavos Jun 27 '19 at 13:28
  • @PanagiotisKanavos i think a C# solution using Office.Interop assemblies can solve the issue – Yahfoufi Jun 28 '19 at 14:54

2 Answers2

1

C# solution

Recently i started a new project on Git-Hub, which is a class library developed using C#. You can use it to import tabular data from excel, word , powerpoint, text, csv, html, json and xml into SQL server table with a different schema definition using schema mapping approach. check it out at:

You can follow this Wiki page for a step-by-step guide:

SSIS Solution

If you are looking to use SSIS you can refer to my answer on the following topic:

Hadi
  • 31,125
  • 9
  • 49
  • 111
-1

In Excel, format all the troublesome cells as Text; all columns are formatted as General by default. Now, save your changes are re-try. You should get what you are after. See this link for all details.

Nazim Kerimbekov
  • 3,965
  • 6
  • 23
  • 48
ASH
  • 15,523
  • 6
  • 50
  • 116
  • This will convert strongly-typed dates and numbers into *localized* strings making it *harder* to import the data. It's probably *because* of strings that SSIS runs into trouble – Panagiotis Kanavos Jun 27 '19 at 13:30
  • Well, I haven't used SSIS in several years, but as I recall, that fixed any/all issues I encountered. Also, add IMEX=1 to the end of your Excel driver connection string. This will allow Excel to read the values as Unicode. See the link below for more details. https://social.msdn.microsoft.com/Forums/sqlserver/en-US/62dafecd-f3ef-4cde-9e49-8c3aafe84f4e/problem-in-excel-datatype-in-ssis?forum=sqlintegrationservices – ASH Jun 27 '19 at 13:47
  • how are you going to handle *date strings* then? And no, IMEX=1 won't make Excel read anything as Unicode. Excel files *are* Unicode - `xlsx` is a zip package containing XML files – Panagiotis Kanavos Jun 27 '19 at 13:48
  • I have tried changing the IMEX=1 and also changing regedit but same issue persist and also i have to load the files of each of size 500MB to 1 GB per load. – Chandra Sekhar Jun 27 '19 at 15:24
  • I am not looking only into ssis but any tool or scripting which i can load the file faster, Please suggest – Chandra Sekhar Jun 27 '19 at 15:26
  • Bulk Insert: https://www.red-gate.com/simple-talk/sql/learn-sql-server/bulk-inserts-via-tsql-in-sql-server/ – ASH Jun 28 '19 at 04:36