January 31, 2012 at 7:30 am
Hi
I have a table with 4 columns and I am exporting the table data to CSV and i am exporting data delimited by | (pipe) but after exporting the data, Data rows are getting missmatch (column 4 data is getting appear in 1st,2nd and 3rd columns also) due which i am unable Import the same data to table and it was causing data type mismatch.
Ex:
Original Sql server table data
ID , name,address
1 ,abc, KPHP,hyderabad. pin:500044,india
After exporting data to a CSV file delimited by |:
ID|name|address
1|abc|KPHP,hyderabad.
pin:500044,india
Here address data scattered and showing under ID and Name columns also.
Due to which i am unable to import same .csv file to db.
how can i resolve it and my address column data should not scattered.
January 31, 2012 at 7:39 am
My guess is you have carriage returns char(13) or line feeds char(10) in your original data.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 31, 2012 at 7:53 am
hi Sean Lange,
I have a tab separation is column data where it was
January 31, 2012 at 8:09 am
Like I said I suspect you have carriage returns in your data. Did you evaluate your data to see?
select * from table where address like '%' + char(13) + '%' or address like '%' + char(10) + '%'
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 1, 2012 at 12:11 am
Hi Sean,
i found there is tab separation in my column data.i suspect that due to tab space in the column data it was happening.
how can fetch the data in correct format
February 1, 2012 at 8:07 am
If you simply want to remove the tab you can use replace.
replace(ColumnWithTabs, char(9), '')
That help?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 1, 2012 at 11:43 pm
It really working fantastic solution........ many thanks.
February 2, 2012 at 9:04 am
You're welcome. Glad that worked for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply