Results 1 to 14 of 14
  1. #1
    Join Date
    Aug 2002
    Posts
    46

    Unanswered: Spaces delimiters in BCP problem

    Hi,
    I have a text file contains many rows of data as follows:

    03 5000189363069 00004.50 00022.50 00001.00 00 555 22
    03 5000189009233 00008.70 00021.75 00001.93 00 655 22
    03 4008400102528 00014.99 00074.95 00003.32 00 444 22
    etc....

    As you've noticed the delimeter between data is spaces.

    All I need to do is to copy this text file (as it is) into a single column of a Staging table.
    For that i use the following stored procedure:

    CREATE PROCEDURE Lan_BCP_StagingTable
    @Filestr nvarchar(256)
    AS
    EXEC ('BULK INSERT Staging_Table From ''' +@Filestr + '''')

    It copies all the data BUT the spaces between data is different from the text file and the location of each data is critical for me.
    In cases of different spaces i get '??????'.

    How can i use BCP (its fast and good for me) and to maintaine same number of spaces as in the text file?
    The above text file is only an example (the original has more data in each row).
    Thanks
    Yossi

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Okay, I copied your few rows to a text file and tride to BCP the data into a test table and got no problems.

    1. create table test (c1 varchar(8000))
    2. from a cmd.exe prompt "bcp research.dbo.test in test.txt -c -S <myserver> -T"
    3. select * from test produced:

    c1
    ------------------------------------------------------------
    03 5000189363069 00004.50 00022.50 00001.00 00 555 22
    03 5000189009233 00008.70 00021.75 00001.93 00 655 22
    03 4008400102528 00014.99 00074.95 00003.32 00 444 22


    I suspect your text file is delimited with somthing other than an ascii 32. Try BCPing the attached and see if you still have problems
    Attached Files Attached Files
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Aug 2002
    Posts
    46
    Originally posted by Paul Young
    Okay, I copied your few rows to a text file and tride to BCP the data into a test table and got no problems.

    1. create table test (c1 varchar(8000))
    2. from a cmd.exe prompt "bcp research.dbo.test in test.txt -c -S <myserver> -T"
    3. select * from test produced:

    c1
    ------------------------------------------------------------
    03 5000189363069 00004.50 00022.50 00001.00 00 555 22
    03 5000189009233 00008.70 00021.75 00001.93 00 655 22
    03 4008400102528 00014.99 00074.95 00003.32 00 444 22


    I suspect your text file is delimited with somthing other than an ascii 32. Try BCPing the attached and see if you still have problems
    I will work on it.
    Many thanks Paul.

  4. #4
    Join Date
    Aug 2002
    Posts
    46
    Originally posted by Paul Young
    Okay, I copied your few rows to a text file and tride to BCP the data into a test table and got no problems.

    1. create table test (c1 varchar(8000))
    2. from a cmd.exe prompt "bcp research.dbo.test in test.txt -c -S <myserver> -T"
    3. select * from test produced:

    c1
    ------------------------------------------------------------
    03 5000189363069 00004.50 00022.50 00001.00 00 555 22
    03 5000189009233 00008.70 00021.75 00001.93 00 655 22
    03 4008400102528 00014.99 00074.95 00003.32 00 444 22


    I suspect your text file is delimited with somthing other than an ascii 32. Try BCPing the attached and see if you still have problems
    Hi Paul,
    I have tried with your text file and its good.
    But as i shifted the last field (22) around 20 spaces forward, the result were different in the table and thats my problem.
    Change the data so the spaces between values is more then 10 spaces.
    Tell me what you think.

  5. #5
    Join Date
    Aug 2002
    Posts
    46
    Originally posted by Paul Young
    Okay, I copied your few rows to a text file and tride to BCP the data into a test table and got no problems.

    1. create table test (c1 varchar(8000))
    2. from a cmd.exe prompt "bcp research.dbo.test in test.txt -c -S <myserver> -T"
    3. select * from test produced:

    c1
    ------------------------------------------------------------
    03 5000189363069 00004.50 00022.50 00001.00 00 555 22
    03 5000189009233 00008.70 00021.75 00001.93 00 655 22
    03 4008400102528 00014.99 00074.95 00003.32 00 444 22


    I suspect your text file is delimited with somthing other than an ascii 32. Try BCPing the attached and see if you still have problems
    Paul,
    I tried some thing and its interesting.
    Modifying the test.txt, if you increase the spaces between data using the TAB it will cause the '?????' to be apear and the spaces would be different.
    But if you modify the position of each data using the space bar only it will be o.k.
    The text file that I am getting can be with Tabs or not.
    Can i do some thing with that?
    Thanks again

  6. #6
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Attach a short copy of the text file with tabs, the one giving the '???', I think I know a solution but want to see exactly what you are working with.
    Paul Young
    (Knowledge is power! Get some!)

  7. #7
    Join Date
    Aug 2002
    Posts
    46
    Originally posted by Paul Young
    Attach a short copy of the text file with tabs, the one giving the '???', I think I know a solution but want to see exactly what you are working with.
    Hi Paul,
    I didn't get any text file. Did you sent some thing?
    I can see now that all my problems is the damm TABS.
    Do you have any solution that will cause my copy not to be effected by Tabs?
    Thanks

  8. #8
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    I was hopeing that YOU would attach the text file being BCPed into your DB, the one with the Tabs.
    Paul Young
    (Knowledge is power! Get some!)

  9. #9
    Join Date
    Aug 2002
    Posts
    46
    Originally posted by Paul Young
    I was hopeing that YOU would attach the text file being BCPed into your DB, the one with the Tabs.
    Line1 and Line3 are copyied well but Line2 is with the tabs.
    Good luck
    Attached Files Attached Files

  10. #10
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    The big problem I see with removing the tabs is knowing the nubmer of spaces to substitute. The tabing is inconsistant.

    Can you strip the tabs at the file source?
    Are the number of tabs between each potential column consistant?
    Paul Young
    (Knowledge is power! Get some!)

  11. #11
    Join Date
    Aug 2002
    Posts
    46
    Originally posted by Paul Young
    The big problem I see with removing the tabs is knowing the nubmer of spaces to substitute. The tabing is inconsistant.

    Can you strip the tabs at the file source?
    Are the number of tabs between each potential column consistant?
    Number of tabs is incosistent unfortunatly.
    What is striping the tabs?
    If you would know number of tabs, How would it help?

  12. #12
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    When I said "Striping the tabs" I was thinking that what ever generates your BCP file might have the ability to remove the tabs OR convert the tabs to the appropriate number of spaces. For example, my editor that can de-tabify a file preserving the column spacing.

    As for the number of tabs, It looks like you have 10 columns of data with the tabs appearing in the last 3 or 4 columns. If you know the number of tabs between columns to could adjust the number of spaces without having to know if there is any data for the column, line #2 in the data file you attached. However since the number of tabs is inconsistent this would nto work.


    Could the file be saved with delimiters other than a space?
    Paul Young
    (Knowledge is power! Get some!)

  13. #13
    Join Date
    Aug 2002
    Posts
    46
    Originally posted by Paul Young
    When I said "Striping the tabs" I was thinking that what ever generates your BCP file might have the ability to remove the tabs OR convert the tabs to the appropriate number of spaces. For example, my editor that can de-tabify a file preserving the column spacing.

    As for the number of tabs, It looks like you have 10 columns of data with the tabs appearing in the last 3 or 4 columns. If you know the number of tabs between columns to could adjust the number of spaces without having to know if there is any data for the column, line #2 in the data file you attached. However since the number of tabs is inconsistent this would nto work.


    Could the file be saved with delimiters other than a space?
    Hi,
    The text file is created by an outside program, Not my company.
    I will check wether the data can be with delimiters.
    I would like to shift to the question with the Id column if I may.
    Your solution is great and working.
    I want to check with you again a different approach.
    I have a table (FreeId) that contain 1 column. this column is holding only 1 value and that is the next Id that should be inserted to Product table each time we add new record.
    The best scenario for me would be:
    A trigger in Product table that will insert into LabelId field the value from FreeId table each time new record added.
    thats easy if you insert new records one at a time.
    How can we do it if we use 'Insert Into' command?
    1.Remember LabelId is PK and as I couldnt insert isnull(max(LabelId) +1,1) to the 'Insert into' command.
    2.If the trigger will be invoked it has to add to each new record value of one, so the LabelId will be consecutive.

    Can I do all that?????
    Hope you understud all that.
    Yossi

  14. #14
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    I will respond to the ID column on your other thread.
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •