0

I receive a csv daily from an external company capturing feedback for us.

Unhelpfully the csv headings vary. There could be any combination of headers. For example:

day1: heading1, heading2, heading3, heading4, heading5

day2: heading1, heading2, heading4, heading6

day3: heading1, heading2, heading7, heading8

day4: heading1, heading2, heading3, heading4, heading5

We have 18 months (no one has done anything with this data until now) of backlog files and so far I have found 22 different combinations...

I know what all the potential headings will be so I can have an SQL table that contains all the appropriate columns but I am struggling to handle the variance for import.

Is there a way to handle this in SSIS? I have looked at bcp and openrowset but I can't seem to make it work.

As this file is received daily I want an sql job that imports them to my table for reporting. I can only create an SSIS package that will import a fixed flat file but I need it to be dynamic. Worst case scenario would be to have 22+ different SSIS packages and run a specific 1 for the given headings available but then I would need a way of automatically reading the structure of the csv to decide which package to use.

I'm lost, so does anyone have any direction?

SQL 2016 (13.0.5622.0)

Many thanks

2
  • Unhelpfully the csv headings vary. Does headings are stored in CSV (in the 1st row, for example), or you "simply know"? Does different heading names means different data which must be placed into different columns, or they only looks differently?
    – Akina
    Sep 29, 2020 at 12:09
  • The column headings are always the first row of the csv but what they are and how many there are varies from day to day.
    – Round
    Sep 29, 2020 at 12:12

3 Answers 3

2

Consider a simplified scenario with two different files, each containing different subsets of data.

abc.csv:

a,b,c
1,a,a
2,b,b
3,c,c
4,d,d

abd.csv:

a,b,d
1,a,1
4,d,4
5,e,5
6,f,6

Now, we can use PowerShell to import the file, Select columns into a known, consistent number & order, then export it back to a csv with predictable files:

Import-Csv abc.csv | Select a,b,c,d,e,f,g | Export-Csv abc_cleansed.csv -NoTypeInformation
Import-Csv abd.csv | Select a,b,c,d,e,f,g | Export-Csv abd_cleansed.csv -NoTypeInformation

This will produce two new files that have a common, predictable file format.

abc_cleansed.csv:

"a","b","c","d","e","f","g"
"1","a","a",,,,
"2","b","b",,,,
"3","c","c",,,,
"4","d","d",,,,

abd_cleansed.csv:

"a","b","c","d","e","f","g"
"1","a",,"1",,,
"4","d",,"4",,,
"5","e",,"5",,,
"6","f",,"6",,,

Note: I omitted double-quotes on my initial csv files, and PowerShell has also helpfully added them in. You could remove them, but IMHO this is definitely a beneficial feature for data quality.

Now, you can simply build a data pipeline where you take an input file, do conversion to cleanse the format, then pick up those cleansed files to import them. Depending on your process, you could do this all within a single SSIS package, or build separate data cleansing/data importing processes.

3
  • hey! didja know about -UseQuotes AsNeeded? It's a game changer Sep 29, 2020 at 15:53
  • I low key hate when quotes are only used when needed. I prefer them to be there all the time, thus pretending I have no idea what you are talking about, @PeterVandivier. 🤣🤣
    – AMtwo
    Sep 29, 2020 at 16:06
  • This is working perfectly. Good explanation. Thanks
    – Round
    Sep 30, 2020 at 12:52
2

I would do it like this:

  1. There exists some working table where the data must be placed into. It contains columns for all data which may present in CSVs, with proper names and datatypes.

  2. Create names table with 2 columns: column name used in CSV; according column name in working table. Like

CSV_name    | table_name
------------|-----------
ID          | id
user_id     | id
Employee_id | id
name        | first_name
FirstName   | first_name
...         | ...

Each column is defined as unique.

  1. Create a procedure which:
  • Loads first row from CSV file (the file is renamed to some pre-defined name or its is got as a parameter), parses it to separate names, then adds them into CSV_name ignoring duplicate errors, then counts the amount of names in names table with NULL in table_name.

  • If there are CSV_name rows with table_name IS NULL then procedure types a message and breaks. Operator looks names table and fills table_name column with according values, then calls the procedure again.

  • If all CSV_names have according table_name then procedure creates temporary table and loads the whole data into it. After data loading the procedure builds INSERT INTO query text and copies the data into working table. Alternatively correct importing query text may be created/executed in one step.

0

Good evening friend. Have you heard the good news about DBATools?

mormons like dbatools

Import-DbaCsv may be the function for you. I wrote a patch a while back that auto-detects and maps column names based on source-and-target under certain constraints. It may be exactly what you need.

2
  • Please do not post an advertisement here. If you have a link to a tool then post it as comment but o not post link-only answers
    – miracle173
    Sep 30, 2020 at 7:40
  • Not an ad, just an answer with whimsy. I'm neither a current contributor to the project nor have I used it professionally for the last year and a half. Please feel free to discuss further in Database Administrators Chat. Sep 30, 2020 at 7:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.