Loading Multiple csv or xlsx Files into Table

Question

Post reply

Loading Multiple csv or xlsx Files into Table

tinausa

SSCrazy

Points: 2697
More actions
September 21, 2016 at 4:02 pm

#321592

I have several dozen csv files where the data quality is poor, so is difficult to load directly into the SQL Server 2012.
I have written some code to save them as XLSX and am trying to create a package that would load them in a loop.
But I am running Windows 7 64-bit with SQL Server 2012 64-bit and Office 2010 32-bit. I keep getting error on connection string. How do I fix that? I used the string below.
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+@[User::FileName]+";Extended Properties=\"Excel 8.0;HDR=YES\";"
How else can I load these files? Each file has severla hundred thousand rows.
Thanks in advance.

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

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

pietlinden SSC Guru Points: 63501 More actions · Answer 1

pietlinden

SSC Guru

Points: 63501

September 21, 2016 at 9:42 pm

#1902331

For Each [file] Loop?

benscooper01 Valued Member Points: 70 More actions · Answer 2

Have you tried setting the package to run in 32-bit mode? (Project Properties > Config Properties > Debugging > Run64BitRuntime)

I can't remember 100%, but I don't think there's a 64-bit Jet driver, which can cause an SSIS error when connecting. Failing that, could you just load the csv files rather than converting them to xlsx in a For Each loop as pietlinden suggested?

Cheers

Ben

Thom A SSC Guru Points: 99129 More actions · Answer 3

If the problem is still occurring, can you post the error you get?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

tinausa SSCrazy Points: 2697 More actions · Answer 4

When I enter the connection string, I get the folloiwng error:

Exception from HRESULT: 0xC0024108

tinausa SSCrazy Points: 2697 More actions · Answer 5

Unfortunately, I can't. The data is pretty crappy. Lot fo double quotes and numeric columns preceeded by =".

frederico_fonseca SSCoach Points: 16154 More actions · Answer 6

use the ACE driver - 32 and 64bit available and will process all versions of Excel. freely available at microsoft https://www.microsoft.com/en-us/download/details.aspx?id=39358

MMartin1 One Orange Chip Points: 27795 More actions · Answer 7

I am not totally certain but you may want to install the appropriate version of the provider (32 bit, 64 bit) on both the machine where Office is installed and the machine where SSIS is installed.

----------------------------------------------------

Michael L John One Orange Chip Points: 27054 More actions · Answer 8

One suggestion, import then into an Access database first, then pull the data from there.

The ACE connection issues go away, and Access does a better job pulling spread sheets in then SSIS.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

MMartin1 One Orange Chip Points: 27795 More actions · Answer 9

Michael L John (9/27/2016)
and Access does a better job pulling spread sheets in then SSIS.

So you would not use SSIS to pull into Access? I think the user wants to schedule automation here with SSIS and no user involvement. You can still import into an Access database within SSIS.

----------------------------------------------------

tinausa SSCrazy Points: 2697 More actions · Answer 10

I don't need to do anything in Access. I have gazillion xlsx files that I need into SQL server table and thinking of using SSIS. But the foreach loop is giving me trouble.

Hope that clarifies. Thanks in advance.

Tina

Thom A SSC Guru Points: 99129 More actions · Answer 11

I know you've said that the data quality is poor, but what does converting the files from csv to xlsx actually achiev,e(and how are you doing so?

Just loading a csv file in Excel and then saving it (as xlsx), won't actually improve data quality at all.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

RonKyle SSC-Dedicated Points: 31556 More actions · Answer 12

I have several dozen csv files where the data quality is poor, so is difficult to load directly into the SQL Server 2012.

I have trouble imaging this, and certainly to the point where importing it into Excel first would solve the issues. Your first job is simply to possess the data. Just get it into a table in the current condition. Start fixing it from there. You may stage through several tables to do this, but that's okay.

MMartin1 One Orange Chip Points: 27795 More actions · Answer 13

RonKyle (9/28/2016)
I have several dozen csv files where the data quality is poor, so is difficult to load directly into the SQL Server 2012.
I have trouble imaging this, and certainly to the point where importing it into Excel first would solve the issues. Your first job is simply to possess the data. Just get it into a table in the current condition. Start fixing it from there. You may stage through several tables to do this, but that's okay.

And for these types of situations I would also go with the staging table having something like varchar(255) for all the fields, as you state, just to get the data into a table where you can begin to study the data quality.

----------------------------------------------------

lnoland SSCommitted Points: 1753 More actions · Answer 14

tinausa (9/26/2016)
When I enter the connection string, I get the folloiwng error:
Exception from HRESULT: 0xC0024108

Does the suggestion at this link https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ae994b54-ce9d-4826-8635-efce44668eef/cannot-import-multiple-xlsx-files-using-foreach-loop?forum=sqlintegrationservices solve your problem?

- Les