TSV to Table

Question

TSV to Table

-Shree

SSC Eights!

Points: 831
More actions
April 8, 2010 at 10:03 am

#140162

Hi,
Am trying to convert tsv file to table for performance analysis, but am unable to do that:angry:
have tried
1. Converting TSV - to Excel- and then importing to table
this worked but truncated few columns (more than 25 columns!!)
2. Imported TSV directly to table -- encountered error
3. Coverted TSV - to CSV - to table -- same error
4. Converted TSV- to TXT - to table -- same error
5. Converted TSV- toTXT- to EXCEL - to table - same error
now other option which i think will work is creating a table of similar columns manually and then copy it from excel to table..but its gonna be time consuming and will crash down my machine!!
Can any one please help me in this.
Thanks
Shree

Viewing 7 posts - 1 through 6 (of 6 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

the error is related to the row size of the insert; > 8062

for example, if i have 25 columns, and they are all defined as varchar(500), 25 X 500 = 12500, which is bigger thant he allowed 8062 chars for an update.

if you change the destination table to be varchar(max) for most of the columns , i believe it will work.

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!

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 2

-Shree (4/8/2010)
Hi,
Am trying to convert tsv file to table for performance analysis, but am unable to do that:angry:
have tried
1. Converting TSV - to Excel- and then importing to table
this worked but truncated few columns (more than 25 columns!!)
2. Imported TSV directly to table -- encountered error
3. Coverted TSV - to CSV - to table -- same error
4. Converted TSV- to TXT - to table -- same error
5. Converted TSV- toTXT- to EXCEL - to table - same error
now other option which i think will work is creating a table of similar columns manually and then copy it from excel to table..but its gonna be time consuming and will crash down my machine!!
Can any one please help me in this.
Thanks
Shree

Create the table first and then use BULK INSERT.

--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)

-Shree SSC Eights! Points: 831 More actions · Answer 3

Hi,

Thanks for the replies. 🙂

tried both and it worked!!:Wow:

Thanks a tonne !!

-Shree

-Shree SSC Eights! Points: 831 More actions · Answer 4

hi again,

Though am able to convert tsv to table now..but was wondering why did it truncated the columns when i converted tsv- to excel -to table?

Total number of columns were 373 but after import they were just 256 i.e. 117 columns missing..(sry i had said more than 25 in earlier post, i should have been more specific on that) and total number of rows are 8461.

Can any one help me in understanding this.

Thanks

Shree

K Cline Ten Centuries Points: 1391 More actions · Answer 5

Though am able to convert tsv to table now..but was wondering why did it truncated the columns when i converted tsv- to excel -to table?
Total number of columns were 373 but after import they were just 256 i.e. 117 columns missing...

The maximum number of columns in an in pre-Excel 2007 worksheets is 256.

"The Excel 2007 "Big Grid" increases the maximum number of rows per worksheet from 65,536 to over 1 million, and the number of columns from 256 (IV) to 16,384 (XFD)."

Link to the source web page.

-Shree SSC Eights! Points: 831 More actions · Answer 6

Thanks for the reply. Checked the link, really helpful.

Thanks once again!