Post reply

Loading Multiple csv or xlsx Files into Table

  • September 28, 2016 at 1:30 pm

    #1903770

    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.

  • September 28, 2016 at 1:32 pm

    #1903771

    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.

  • September 28, 2016 at 1:44 pm

    #1903776

    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.

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

  • September 28, 2016 at 1:45 pm

    #1903777

    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.

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

  • September 28, 2016 at 2:46 pm

    #1903792

    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.

  • September 28, 2016 at 4:17 pm

    #1903804

    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.:-(

  • September 28, 2016 at 4:17 pm

    #1903805

    Would be nice if I could land first:-(

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

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