SQL Import Wizard errors on importing a psv file

Question

SQL Import Wizard errors on importing a psv file

I am trying to import a psv (pipe delimited csv) into Microsoft SQL Server 2008R2 Express database table.

There are only two fields in the psv, each field has more than 1000 characters.

In the import wizard, I have the following settings:

Double checked in the mapping:

Note I set the option of Ignore on fail/truncate:

and as usual, I get an error:

Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "Comm" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.". (SQL Server Import and Export Wizard)

UPDATE:

So, following @Marc's suggestion, though very/extremely reluctant, I spent 3 hours or so to finally get SQL2014 installed on my computer and am hoping to import the psv. As expected, error shows up again:

I really cannot understand why company like Microsoft did not do thorough QAT on their products?!

sql-server-2014

asked on Stack Overflow Feb 22, 2020 by

mdivk • edited Feb 22, 2020 by

mdivk

2 Answers

After being tortured by Microsoft for the whole morning, I finally got this task done, for the future readers, you can follow the steps below to import a csv/psv data source into your sql:

Import the CSV/PSV to an Access Database. Note, must be saved to the mdb type (yes, the type from 20th century), you might want to read my story here: how to import psv data into Microsoft Access
In your SQL (mine is 2014), start the Import Wizard and select the data source type (ACCESS) and the file. Why you have to use mdb type of access database? Here you will see there is no option in SQL 2014 for accdb type of access database.

DO NOT forget to select the right Destination (yes, even though you started the wizard by right click on the destination database and chose Import), you want to select the last option: SQL Native Client 11.0. That will show up the SQL2014 and the database.

Now that the import can be completed as expected.

Thanks to the great design logic in this SQL (2014? No, essentially no change compared to 2008), what a humble expectation and requirement!!! it costs me 4-5 hours to complete.

answered on Stack Overflow Feb 22, 2020 by

mdivk

Alternatively, you can use bulk insert to import any flat file.

if (object_id('dbo.usecase1') is not null)
    drop table dbo.usecase1
go

create table dbo.usecase1
(
    Descr nvarchar(2000) null,
    Comm nvarchar(2000) null
)
go

bulk insert dbo.usecase1
from 'C:\tmp\usecase0.psv'
with (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
)

go

BULK INSERT (Transact-SQL)

answered on Stack Overflow Feb 22, 2020 by

OO7

User contributions licensed under CC BY-SA 3.0