Post reply

bulk import help please

  • October 25, 2012 at 12:01 pm

    #283565

    Hi all,

    I need to be able to import foreight language data into sql server 2K8R2 from csv file.

    Here is the table:

    CREATE TABLE [dbo].[ept](

    [f_id] [int] NULL,

    [t_id] [int] NULL,

    [l_id] [nvarchar](2) NULL,

    [t] [nvarchar](max) NULL,

    [a_exp] [char](1) NOT NULL,

    [dep] [char](1) NOT NULL,

    [f] [char](1) NOT NULL,

    )

    Here is the sample of the file:

    872,89801,'cs','Vyberte pole, podle kterého chcete vyhledávat a pak zadejte několik prvních písmen hledat.','y','n','n'

    872,89805,'cs','Maximální počet zaměstnanců byly vráceny. Výsledky hledání omezit vrácené výsledky.','y','n','n'

    Here is the statement:

    BULK

    INSERT ep_tran

    FROM 'c:\temp\ep\ep.csv'

    WITH

    (FIELDTERMINATOR = ',', ROWTERMINATOR = '')

    Here is the error:

    Msg 4864, Level 16, State 1, Line 1

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

    If I remove single quotes(and commas in 4th column) - it works, but I can't do it as the file is too large.

    Is there a way to tell SQL Server to look for values enclosed in single quotes,

    similar to what Oracle has in its external tables?

    Thanks,

  • October 25, 2012 at 12:05 pm

    #1552857

    I am not sure what you mean but if you have SSIS you can build a package that will import multiple CSV files into a table.

  • October 25, 2012 at 12:13 pm

    #1552859

    your data is not just split by commas, is the issue; they are delimited or "Text Qualifed" with single quotes.

    to handle taht gracefully, I think it is much easier to use the Import export wizard in this case, as you can identify the Text Qualifier.

    Otherwise, if you are going to stick with BULK INSERT, you will need to use a format file, so you can show that certain fields are spit by either a comma, or singlequote-comma

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 25, 2012 at 1:01 pm

    #1552884

    I don't use SSIS, but I tried Import Wizard and ... no luck.

    Here is what I've got:

    Executing (Error)

    Messages

    Error 0xc002f210: Drop table(s) SQL Task 1: Executing the query "drop table [dbo].[ep]

    " failed with the following error: "Cannot drop the table 'dbo.ep', because it does not exist or you do not have permission.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    (SQL Server Import and Export Wizard)

    Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "Column 3" 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)

    Error 0xc020902a: Data Flow Task 1: The "output column "Column 3" (22)" failed because truncation occurred, and the truncation row disposition on "output column "Column 3" (22)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

    (SQL Server Import and Export Wizard)

    Error 0xc0202092: Data Flow Task 1: An error occurred while processing file "\\xxx\c$\Temp\ep\ep.csv" on data row 1.

    (SQL Server Import and Export Wizard)

    Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source - ep_csv" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

    (SQL Server Import and Export Wizard)

    I get the first error (table wasn't there), but is the second error telling me?

  • October 25, 2012 at 1:17 pm

    #1552904

    First, it looks like you have a table in your db with that name already. that is your first error. the other error means that the column in your table is not large enough to hold the data you are inserting. Click the advanced tab and you can adjust the output column size.

  • October 25, 2012 at 1:23 pm

    #1552907

    Sorry, misread the first error. Not sure how to help you with that one, the wizard on 2008 does not include a drop table statement as far as I know. Unless you used explicitely stated it in a query

  • October 25, 2012 at 1:24 pm

    #1552908

    I adjusted all column to varchar(max) and got the same error.

  • October 25, 2012 at 1:31 pm

    #1552911

    I got nothin, if you specify text delimiter as " ' ", then it will not recognize a comma as a delimiter if it is within single quotes. Could be the problem is with your data.

  • October 25, 2012 at 1:47 pm

    #1552921

    I specified "text qualifier" as single quote and it understands it (I can see it in preview), but it won't load the data.

    Keeps failing with the same msg

  • October 25, 2012 at 1:54 pm

    #1552929

    is your data really ending in CrLf, or could it be a file form UNIX, where it's ending in just CHAR(10)? (LF)?

    that would explain the errors.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 25, 2012 at 2:04 pm

    #1552937

    Ok, I am trying to import a single row. I believe the data comes from Python, so I did change "row delimiter" to "LF", but ... same error about data conversion for column 3 .....

    That column has 2 characters in it: 'cs'. That's it.

  • October 25, 2012 at 4:01 pm

    #1552974

    I believe the errors I am getting are related to the fact that I am importing a string in Czech Republic language.

    What codepage do I use to load Czech?

Viewing 12 posts - 1 through 11 (of 11 total)

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