Loading Multiple csv or xlsx Files into Table

Question

Loading Multiple csv or xlsx Files into Table

Viewing 7 posts - 16 through 21 (of 21 total)

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

tinausa SSCrazy Points: 2697 More actions · Answer 1

It takes out some of that stuff and puts the data in neaty columns, I looked at csv files nad noticed that it was splitting columns at commas in the cell value and alsoo eliminates =" preceding any numeric field.

I would do it either way - csv or xlsx - if I could make it work. Thanks.

tinausa SSCrazy Points: 2697 More actions · Answer 2

I tried that but some fields were getting split at commas in cell value.

Can't have that happen.

The goal is to get the data in somehow and then I can convert fields to numbers.

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

You are in a tight spot here. You have text files with commas as the field separators and some of those fields are strings with commas within them. You cant have both without a text qualifyer. I prefer using a different delimiter altogether. The pipe | is a good one as I dont see it used in the normal course of string data. Can you have the text data files regenerated? They should not have used csv if there are valid commas in the data (like name suffixes and dollar amounts that are formated ). Otherwise you will have to employ a lot of manual effort to get this sorted out.

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

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

Another option you can try is the fixed width delimiter in SSIS. THere is no guarantee here though as it totally depends on the way your data is laid out.

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

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

I would also go with the staging table having something like varchar(255) for all

I would use an nvarchar(255) in the landing tables. I may cast it in the staging tables to varchar if the data justifies the choice, which it often does.

tinausa SSCrazy Points: 2697 More actions · Answer 6

I fully understand, but the person/s who created these files did not nad i have zero chance of making them redo anything. So I just have to live with it.:-(

tinausa SSCrazy Points: 2697 More actions · Answer 7

tinausa

SSCrazy

Points: 2697

September 28, 2016 at 4:17 pm

#1903805

Would be nice if I could land first:-(