Post reply

Dataimport from csv-file

  • May 24, 2010 at 2:35 am

    #221440

    When I import sata from csv-files to a database in MSSQL Server 8 I have the following problems:

    1. I have a row in the csv-file with the text ´42" Plasma TV´ in one column and just that row is not imported to my char-column in the database. I think 42" is the problem but I don´t know how to get it work.

    2. In another table I have namnes with the Swedish characters å, ä, ö, and these are not imported correct.

    3. When I import customerdata to a NOT NULL customer table I got the following error message:

    The OLE DE provider "BULK" for linked server "null" reported an error. Cannot fetch a row from OLE DE provider BULK for linked server.

    I suppose that it depends on the fact that the NOT NULL customer-ID refers to a personal-ID NULL in another table with.

    I use this code for import:

    BULK INSERT customer

    FROM 'c:\.......'

    WITH (

    FIRSTEROW=2,

    FIELDTERMINATOR=';'

    ROWTERMINATOR=''

    GO

    Can anyone help me to solve these problems?

    Gunilla

  • May 24, 2010 at 3:08 am

    #1171427

    You should add the following two options to import unicode data:

    CODEPAGE = 'RAW'

    DATAFILETYPE = 'widechar'

    That should help you to import your Swedish character (as long as the target table columns are of NVARCHAR type as well...) and probably also will help you to import your '42" Plasma TV'.

    Regarding your customerID issue: it's hard to tell without seeing table def and sample data. But I expect the customerID not to be an IDENTITIY column and you also don't have it in your source file. If that's the case you might need to import the data into a staging table and copy it to your final table after validation (which is the better way in the first place...).

    If you need further assistance please provide table definition including any constraints/triggers or the like together with a sample file holding fake data but demostrate the issue you're struggling with. Finally, please include what you've tried so far.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • May 24, 2010 at 3:42 am

    #1171439

    Thank you for the answer.

    lmu92 (5/24/2010)

    You should add the following two options to import unicode data:

    CODEPAGE = 'RAW'

    DATAFILETYPE = 'widechar'

    That should help you to import your Swedish character (as long as the target table columns are of NVARCHAR type as well...) and probably also will help you to import your '42" Plasma TV'.

    I have now created a target table with NCHAR type columns and tried bulk insert with the following code:

    BULK INSERT kurs.produkt

    FROM 'c:\SQL_filer\produkt.csv'

    WITH (

    CODEPAGE = 'RAW'

    DATAFILETYPE = 'widechar'

    FIRSTROW=2,

    FIELDTERMINATOR = ';',

    ROWTERMINATOR = '')

    GO

    and I get this error message:

    Bulk load: DataFileType was incorrectly specified as widechar. DataFileType will be assumed to be char because the data file does not have a Unicode signature.

    Bulk load: DataFileType was incorrectly specified as widechar. DataFileType will be assumed to be char because the data file does not have a Unicode signature.

    Msg 4864, Level 16, State 1, Line 2

    Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 2 (chef_id).

    and when I use the same code to a table with char I get this error message:

    Bulk load: DataFileType was incorrectly specified as widechar. DataFileType will be assumed to be char because the data file does not have a Unicode signature.

    Bulk load: DataFileType was incorrectly specified as widechar. DataFileType will be assumed to be char because the data file does not have a Unicode signature.

    Gunilla

  • May 24, 2010 at 6:05 am

    #1171488

    Hi again,

    I have database file with a customer table containing the following columns:

    customer_id(PK,int,not mnull)

    customerresponsible_id (FK,int, not null)

    firstname (char(29)), not null)

    secondname (char(29)), not null)

    telephonenr (char(15), not null)

    mobiletelephonnr (char(15), not null)

    I try to insert data from a customer.csv with corresponding column names with the following sql-code:

    BULK INSERT cusotmer

    FROM 'c:\customer.csv'

    WITH (

    FIRSTROW=2,

    FIELDTERMINATOR=';'

    ROWTERMINATOR=''

    GO

    And I get the following error:

    Msg 4863, Level 16, State 1, Line 2

    Bulk load data conversion error (truncation) for row 2, column 6 (mobiltelefon).

    Msg 4863, Level 16, State 1, Line 2

    Bulk load data conversion error (truncation) for row 3, column 6 (mobiltelefon).

    Msg 4863, Level 16, State 1, Line 2

    Bulk load data conversion error (truncation) for row 4, column 6 (mobiltelefon).

    Msg 4863, Level 16, State 1, Line 2

    Bulk load data conversion error (truncation) for row 5, column 6 (mobiltelefon).

    Msg 4863, Level 16, State 1, Line 2

    Bulk load data conversion error (truncation) for row 6, column 6 (mobiltelefon).

    Msg 4863, Level 16, State 1, Line 2

    Bulk load data conversion error (truncation) for row 7, column 6 (mobiltelefon).

    Msg 4863, Level 16, State 1, Line 2

    Bulk load data conversion error (truncation) for row 8, column 6 (mobiltelefon).

    Msg 4863, Level 16, State 1, Line 2

    Bulk load data conversion error (truncation) for row 9, column 6 (mobiltelefon).

    Msg 4863, Level 16, State 1, Line 2

    Bulk load data conversion error (truncation) for row 10, column 6 (mobiltelefon).

    Msg 4863, Level 16, State 1, Line 2

    Bulk load data conversion error (truncation) for row 11, column 6 (mobiltelefon).

    Msg 4863, Level 16, State 1, Line 2

    Bulk load data conversion error (truncation) for row 12, column 6 (mobiltelefon).

    Msg 4865, Level 16, State 1, Line 2

    Cannot bulk load because the maximum number of errors (10) was exceeded.

    Msg 7399, Level 16, State 1, Line 2

    The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.

    Msg 7330, Level 16, State 2, Line 2

    Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

    (0 row(s) affected)

    What do I do wrong?

    Gunilla

  • May 24, 2010 at 6:23 am

    #1171503

    See my previous post:

    If you need further assistance please provide table definition including any constraints/triggers or the like together with a sample file holding fake data but demonstrate the issue you're struggling with. Finally, please include what you've tried so far.

    Maybe there are a few people out there just waiting for you to provide a few more details.

    Regarding your latest post: Seems like you have values in the column mobiltelefon that exceed the specified length of 15 char. Again, just guessing...


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • May 24, 2010 at 7:07 am

    #1171531

    Here come some more information for the databasetable:

    CREATE TABLE kurs.kund

    (kund_idINT IDENTITY(1,1),

    kundansvarig_idINT NOT NULL,

    fornamnCHAR(20) NOT NULL,

    efternamn CHAR(20) NOT NULL,

    telefonnr CHAR(15) NOT NULL,

    mobiltelefon CHAR(15) NOT NULL,

    ring_ej_flagga BIT NOT NULL)

    GO

    ALTER TABLE kurs.kund

    ADD CONSTRAINT pk_kund PRIMARY KEY CLUSTERED (kund_id)

    GO

    ALTER TABLE kurs.kund

    ADD CONSTRAINT fk_kundtopersonal FOREIGN KEY(kundansvarig_id)

    REFERENCES kurs.personal (personal_id)

    GO

    The file from which I inseert data has 2000 row, I attach a faked file with 6 rows. It is not possible to attach csv-files så I attach the xlsx-file from which I in Excel have saved a commaseparated csv-file.

    I have tried to import date with the following code:

    BULK INSERT kurs.kund

    FROM 'c:\SQL_filer\kund_fake.csv'

    WITH (

    FIRSTROW=2,

    FIELDTERMINATOR = ';',

    ROWTERMINATOR = '')

    GO

    and then I get the error described above.

    I hope it is more clear now.

    Gunilla

  • May 24, 2010 at 7:21 am

    #1171539

    varkstad (5/24/2010)

    Thank you for the answer.

    lmu92 (5/24/2010)

    You should add the following two options to import unicode data:

    CODEPAGE = 'RAW'

    DATAFILETYPE = 'widechar'

    That should help you to import your Swedish character (as long as the target table columns are of NVARCHAR type as well...) and probably also will help you to import your '42" Plasma TV'.

    I have now created a target table with NCHAR type columns and tried bulk insert with the following code:

    BULK INSERT kurs.produkt

    FROM 'c:\SQL_filer\produkt.csv'

    WITH (

    CODEPAGE = 'RAW'

    DATAFILETYPE = 'widechar'

    FIRSTROW=2,

    FIELDTERMINATOR = ';',

    ROWTERMINATOR = '')

    GO

    and I get this error message:

    Bulk load: DataFileType was incorrectly specified as widechar. DataFileType will be assumed to be char because the data file does not have a Unicode signature.

    Bulk load: DataFileType was incorrectly specified as widechar. DataFileType will be assumed to be char because the data file does not have a Unicode signature.

    Msg 4864, Level 16, State 1, Line 2

    Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 2 (chef_id).

    and when I use the same code to a table with char I get this error message:

    Bulk load: DataFileType was incorrectly specified as widechar. DataFileType will be assumed to be char because the data file does not have a Unicode signature.

    Bulk load: DataFileType was incorrectly specified as widechar. DataFileType will be assumed to be char because the data file does not have a Unicode signature.

    Gunilla

    Post the first 5 lines of the file making sure that you obfuscate names and phone numbers so they aren't "real". The problem is very likely that the delimiters for the header are not matching the delimiters for the body of the file and (unfortunately) "FIRSTROW" won't make up for that.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 24, 2010 at 7:30 am

    #1171545

    For now I solved this unicode problem when I changed

    42" Plasma TV to 42tum Plasma TV

    but I still don´t get the swedish characters å,ä,ö, correct in the databasetable.

    Gunilla

  • May 24, 2010 at 7:33 am

    #1171550

    varkstad (5/24/2010)

    For now I solved this unicode problem when I changed

    42" Plasma TV to 42tum Plasma TV

    but I still don´t get the swedish characters å,ä,ö, correct in the databasetable.

    Gunilla

    What is the default collation for your server?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 24, 2010 at 7:40 am

    #1171555

    Unfortunately, your sample data don't show either the plasma TV nor any of your special character :crying:

    Anyway, here's what I've found so far:

    1) Column kund_id is defined as IDENTITY(1,1). In order to be able to fill this column with values you'd have to add KEEPIDENTITY to your insert statement.

    2) The number of columns doesn't match between the file and the target table (column ring_ej_flagga is missing iin the file). Several options: a) add the column to the file, b) remove the column from the table (or better yet, staging table) or c) use a fromat file with your bulk insert statement.

    3) I don't have the personal table available so I couldn't use that constraint...

    Here's what I used as a test file:

    kund_id,kundansvarig_id,fornamn,efternamn,telefonnr,mobiltelefon,bit

    1,1,kalle,svensson,012-12345678 ,012-12345678 ,0

    2,1,pelle,pättersson,012-12345678 ,012-12345678 ,0

    3,1,stina,lörsson,012-12345678 ,012-12345678 ,0

    4,1,42" Plasma,persson,012-12345678 ,012-12345678 ,0

    That's the insert statement:

    BULK INSERT kund

    FROM '...'

    WITH (

    FIRSTROW=2,

    KEEPIDENTITY,

    CODEPAGE ='RAW',

    FIELDTERMINATOR = ',',

    ROWTERMINATOR = '')

    /* result set

    11kalle svensson 012-12345678 012-12345678 False

    21pelle pättersson 012-12345678 012-12345678 False

    31stina lörsson 012-12345678 012-12345678 False

    4142" Plasma persson 012-12345678 012-12345678 False

    */

    Seems to work on my machine... What's the collation of the table you're trying to insert into?


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • May 24, 2010 at 7:42 am

    #1171558

    Jeff Moden (5/24/2010)

    varkstad (5/24/2010)

    For now I solved this unicode problem when I changed

    42" Plasma TV to 42tum Plasma TV

    but I still don´t get the swedish characters å,ä,ö, correct in the databasetable.

    Gunilla

    What is the default collation for your server?

    Hmm, how do I know that? I work in Microsoft SQL Server Management Studio with a local host.

    Gunilla

  • May 24, 2010 at 7:47 am

    #1171563

    To get the collation of ...

    the table:

    right click on the kund table in SSMS -> properties -> Extended properties -> collation

    the database:

    right click on the database in SSMS -> properties -> maintenance


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • May 24, 2010 at 8:29 am

    #1171594

    Thank you so much. I deleted the column in the databasetable which hads no information for the moment in the inported file. And then it did work! I think I can add that column in the database when it is needed.

    The collation for the table is Finnish_Swedish_CI_AS

    The collation for the database is the same.

    The PlasmaTV-problem is not in this table. For this I use the table:

    CREATE TABLE kurs.produkt_2

    (produkt2_idINTIDENTITY(1,1),

    namnCHAR(20)NOT NULL,

    provisionNUMERIC(10,2)NOT NULL)

    GO

    ALTER TABLE kurs.produkt_2

    ADD CONSTRAINT pk_produkt2 PRIMARY KEY CLUSTERED(produkt2_id)

    GO

    and do bulk insert like this

    USE gbdatabas

    GO

    BULK INSERT kurs.produkt_2

    FROM 'c:\SQL_filer\produkt_2.csv'

    WITH (

    FIRSTROW=2,

    KEEPIDENTITY,

    FIELDTERMINATOR = ';',

    ROWTERMINATOR = '')

    GO

    with this csv-file:

    produkt2_id;namn;provision

    1;" 42"" Plasma TV ";1000

    2;Nokia 6150 ;500

    3;Telefonabonnemang ;100

    4;"19"" LCD skärm ";500

    5;EU moped ;3000

    6;Fiskleverolja ;100

    7;Vitamintillskott ;1500

    8;VW Golf ;4000

    9;DVD-filmer ;700

    10;CD-Skivor ;2000

    then I get this error: Msg 4863, Level 16, State 1, Line 1

    Bulk load data conversion error (truncation) for row 5, column 2 (namn).

    (9 row(s) affected)

    There is something wrond with the 19" LCD display. The 42" plasma TV appears in the databasetable but not the 19":

    1" 42"" Plasma TV " 1000.00

    2Nokia 6150 500.00

    3Telefonabonnemang 100.00

    5EU moped 3000.00

    6Fiskleverolja 100.00

    7Vitamintillskott 1500.00

    8VW Golf 4000.00

    9DVD-filmer 700.00

    10CD-Skivor 2000.00

    I really don´t understand, but I am very tired now.

    Thanks for your patience.

    Gunilla

    ----------

    I have so far solved all import problems except the produkt import where post

    4;"19"" LCD skärm ";500

    doesn´t appear in the databasetable.

    It is the only post which has an ä, may be that´s the problem although I have done bulk insert with this code:

    BULK INSERT kurs.produkt

    FROM 'c:\SQL_filer\produkt.csv'

    WITH (

    FIRSTROW=2,

    KEEPIDENTITY,

    CODEPAGE='RAW',

    FIELDTERMINATOR = ';',

    ROWTERMINATOR = '')

    GO

    Gunilla

    Hi again,

    Now I have solved even this last problem. Thank you for all help today.

    Gunilla

  • May 25, 2010 at 6:25 pm

    #1172420

    varkstad (5/24/2010)

    Hi again,

    Now I have solved even this last problem. Thank you for all help today.

    Gunilla

    Two way street here, Gunilla. 😉 How did you fix that last problem?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 25, 2010 at 8:53 pm

    #1172448

    There were some blank positions after "19tum LCD skärm" and when I deleted these even this row appeared in the table.

    Gunilla

Viewing 15 posts - 1 through 15 (of 15 total)

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy