1

Is there a way I can import csv files with the proper data types into MSSQL table using PS dba tools?

I'm using this below command but all I get is a table and all the columns are nvarchar(max):

Import-DbaCsv -Path C:\user.csv -SqlInstance $server -Database $DB -AutoCreateTable

I know I can achieve this by SSIS but it's not the best option for me as some of the CSV structures keep changing, +/- Columns.

2 Answers 2

2

For Import-DbaCsv, refer to the help text for the -ColumnMap parameter

By default, the bulk copy tries to automap columns...

For CSV files with variable column headers, Import-DbaCsv will make a best-guess at mapping what goes where (so column names and datatypes matter a lot).

Demo it for yourself.

use tempdb
go
drop table if exists foo;
create table dbo.foo (
     id int not null primary key identity
    ,foo varchar(100) 
    ,bar int 
    ,baz date
    ,bin varchar(100) 
);

Note the difference in columns for foo.csv and foo2.csv

@"
foo,bar,baz
aaa,111,1900-01-01
bbb,222,1900-02-02
ccc,333,1900-03-03
"@ | Set-Content foo.csv

@"
foo,bin,baz
ddd,444,1900-01-04
eee,555,1900-02-05
fff,666,1900-03-06
"@ | Set-Content foo2.csv

$importSpec = @{
    SqlInstance = "localhost"
    Database    = "tempdb"
    Schema      = "dbo"
    Table       = "foo"
}

Import-DbaCsv foo.csv @importSpec -Verbose
Import-DbaCsv foo2.csv @importSpec -Verbose
select * from tempdb.dbo.foo;

Note the columns are properly mapped in the database target

id foo bar baz bin
1 aaa 111 1900-01-01
2 bbb 222 1900-02-02
3 ccc 333 1900-03-03
4 ddd 1900-01-04 444
5 eee 1900-02-05 555
6 fff 1900-03-06 666

Disclaimer: I wrote v1 of the AutoMap logic though it has changed since then.

2
  • Hi Peter Vandivier, thanks for your reply. I like the -column and -columnMap parameters, these will work when an SQL table is already created, they will definitely help me in designing what im looking for. I was looking for a solution that helps me creating the tables with proper data types.
    – SAKA UK
    Commented Aug 31, 2022 at 7:36
  • Is this parameter working? It seem to always return an error "Cannot convert the "=" value of type "System.String" to type "System.Collections.Hashtable" for me
    – Jacob H
    Commented Apr 24 at 12:39
1

The root problem in your use case is that it is not possible for bulk loader to guess correctly column sizes or even data types. You see, in most ETL implementations, column types and lengths are specified well in advance. The loader can trust that the data either fits the destination, or classify it as a broken one.

The loader could not know that, say, row 501 has very long string of 1500 characters in product description, whilst all the other rows have just a few dozen of letters. If it guesses that the column is shorter, row 501 will not load correctly. If it plays it safe and makes everything nvarchar(max), data fits but is inefficient and no type checks are done.

Now, the loader could read through the whole file and keep a count on each column max lengt and data types. Then it could create a table according to these specifications. Then it would read the whole file again, and do the insertion. This would require reading the file twice, which is inefficient and usually not needed since ETL processes rely on pre-agreed file formats. Some tools, such as SQL Server Management Studio's Import wizard do this, kind of. It reads the first 200 rows from a file and base the guess on that piece of data. And that's why I suggested the mismatching data is on row 501, far too far from the sampler component.

There are tools, such as csvkit that can create table structure from a CSV file. Wether it's any good, I have no experience.

1
  • Hi vonPryz, thanks for your reply and detailed explanation. I will give the csvkit a try and get back to you. cheers.
    – SAKA UK
    Commented Aug 31, 2022 at 7:37

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.