Sfrgttk

What caused Doctor Strange to repent of his selfishness and become Earth's protector?

What is the correct interpretation of the Gambling Results table in Xanathar's Guide to Everything?

How do I write a maintainable, fast, compile-time bit-mask in C++?

Badly designed reimbursement form. What does that say about the company?

Why does finding small effects in large studies indicate publication bias?

Why Third 'Reich'? Why is 'reich' not translated when 'third' is? What is the English synonym of reich?

Getting size of dynamic C-style array vs. use of delete[]. Contradiction?

How can a kingdom keep the secret of a missing monarch from the public?

Using 14 ga on part of a 20A circuit?

1730 House how to make Ceiling Level

Is my plan for an air admittance valve ok?

What is the source for this Leonardo Da Vinci quote?

How do I ask to leave because of weather?

How can guns be countered by melee combat without raw-ability or exceptional explanations?

Does changing "sa" password require a SQL restart (in mixed mode)?

Will linear voltage regulator step up current?

I hate taking lectures, can I still survive in academia?

How do I handle a blinded enemy which wants to attack someone it's sure is there?

Arizona laws regarding ownership of ground glassware for chemistry usage

Which was the first story to feature space elevators?

Is it possible to detect 100% of SQLi with a simple regex?

Rudeness by being polite

Ramanujan's radical and how we define an infinite nested radical

Determining whether a system is consistent or inconsistent from the linear dependence of its columns

Can't import flat file into SqlServer

Import CSV file into SQLServerIssue importing large file into SQL serverImport errors importing CSV to SQL Server tableImporting CSV file using SQL Server Import and Export Wizard (SQL Server Express)Error message when importing data into SQL (Import & Export Data Wizard)Import (flat file) into SQL ServerExcel Import ID, Data Type DoubleHow to fix invalid length in all tables of MS SQL Server 2008 (R2) databaseImport in postgres json data in a csv fileHow to correct SQL Server Import and Export Wizard error that says my data conversion length must be between 0 and 4000?

2

I've got this tab-delimited flat file with fifty columns and two million rows. I am trying to import it into Sql Server 2017 Express. (I first tried working with it in Excel... hopeless.)

I see two import paths available: SqlServer Management Studio's Import Flat File command, and the Import and Export Data wizard. Both fail, but in different ways.

With SSMS, it seems to do a very good job of parsing the file and guessing the column types, but it bases the lengths on only the first few hundred rows. One column is long text, so I've tried setting it to nvarchar(4000) or nvarchar(max) or ntext. A few others I knew might be longer than the default guessed length of 50 so I bumped up the sizes. But when I run the import, it says that some column would be truncated or otherwise fail to convert, and it refuses to say which column is the one having trouble.

With the import wizard, on the other hand, it doesn't guess column types at all, and I have to manually set the ones I know. And thankfully, it has a setting that allows you to tell it per column whether to fail on conversion errors or ignore them. But it ignores this setting -- it always fails no matter how you set the tolerance, per column or globally.

The good part is that it does identify a column name where it failed, namely the long text column. But as best I have been able to measure it, there's nothing in it which should have failed. I don't think any value is over 4000 characters (the longest I've located is 2027) and I'm pretty sure the character set is pure ascii-7. In fact, it's been forced to all uppercase.

I also tried importing from the abortive Excel sheet I made. This was thwarted because only the 32 bit importer could see Excel, and only the 64 bit one could see my database instance.

How can I get one or the other of these to allow the import to complete?

sql-server import csv

share|improve this question

edited Feb 17 at 19:09

Paul Kienitz

asked Feb 17 at 18:23

Paul KienitzPaul Kienitz

1113

New contributor

Paul Kienitz is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

2

I've got this tab-delimited flat file with fifty columns and two million rows. I am trying to import it into Sql Server 2017 Express. (I first tried working with it in Excel... hopeless.)

I see two import paths available: SqlServer Management Studio's Import Flat File command, and the Import and Export Data wizard. Both fail, but in different ways.

With SSMS, it seems to do a very good job of parsing the file and guessing the column types, but it bases the lengths on only the first few hundred rows. One column is long text, so I've tried setting it to nvarchar(4000) or nvarchar(max) or ntext. A few others I knew might be longer than the default guessed length of 50 so I bumped up the sizes. But when I run the import, it says that some column would be truncated or otherwise fail to convert, and it refuses to say which column is the one having trouble.

With the import wizard, on the other hand, it doesn't guess column types at all, and I have to manually set the ones I know. And thankfully, it has a setting that allows you to tell it per column whether to fail on conversion errors or ignore them. But it ignores this setting -- it always fails no matter how you set the tolerance, per column or globally.

The good part is that it does identify a column name where it failed, namely the long text column. But as best I have been able to measure it, there's nothing in it which should have failed. I don't think any value is over 4000 characters (the longest I've located is 2027) and I'm pretty sure the character set is pure ascii-7. In fact, it's been forced to all uppercase.

I also tried importing from the abortive Excel sheet I made. This was thwarted because only the 32 bit importer could see Excel, and only the 64 bit one could see my database instance.

How can I get one or the other of these to allow the import to complete?

sql-server import csv

share|improve this question

edited Feb 17 at 19:09

Paul Kienitz

asked Feb 17 at 18:23

Paul KienitzPaul Kienitz

1113

New contributor

Paul Kienitz is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

I've got this tab-delimited flat file with fifty columns and two million rows. I am trying to import it into Sql Server 2017 Express. (I first tried working with it in Excel... hopeless.)

I see two import paths available: SqlServer Management Studio's Import Flat File command, and the Import and Export Data wizard. Both fail, but in different ways.

With SSMS, it seems to do a very good job of parsing the file and guessing the column types, but it bases the lengths on only the first few hundred rows. One column is long text, so I've tried setting it to nvarchar(4000) or nvarchar(max) or ntext. A few others I knew might be longer than the default guessed length of 50 so I bumped up the sizes. But when I run the import, it says that some column would be truncated or otherwise fail to convert, and it refuses to say which column is the one having trouble.

With the import wizard, on the other hand, it doesn't guess column types at all, and I have to manually set the ones I know. And thankfully, it has a setting that allows you to tell it per column whether to fail on conversion errors or ignore them. But it ignores this setting -- it always fails no matter how you set the tolerance, per column or globally.

The good part is that it does identify a column name where it failed, namely the long text column. But as best I have been able to measure it, there's nothing in it which should have failed. I don't think any value is over 4000 characters (the longest I've located is 2027) and I'm pretty sure the character set is pure ascii-7. In fact, it's been forced to all uppercase.

I also tried importing from the abortive Excel sheet I made. This was thwarted because only the 32 bit importer could see Excel, and only the 64 bit one could see my database instance.

How can I get one or the other of these to allow the import to complete?

sql-server import csv

share|improve this question

edited Feb 17 at 19:09

Paul Kienitz

asked Feb 17 at 18:23

Paul KienitzPaul Kienitz

1113

New contributor

Paul Kienitz is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

edited Feb 17 at 19:09

Paul Kienitz

asked Feb 17 at 18:23

Paul KienitzPaul Kienitz

1113

New contributor

Paul Kienitz is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

edited Feb 17 at 19:09

Paul Kienitz

asked Feb 17 at 18:23

Paul KienitzPaul Kienitz

1113

New contributor

Paul Kienitz is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked Feb 17 at 18:23

Paul KienitzPaul Kienitz

1113

New contributor

Paul Kienitz is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked Feb 17 at 18:23

Paul KienitzPaul Kienitz

1113

2 Answers
2

active

oldest

votes

1

I worked with tsv files before and had a similar issue. When you're importing a flat file, I would suggest you change the way SQL Server interprets the column
https://user-images.githubusercontent.com/35179176/34650736-39eaaab0-f3c6-11e7-9706-377c5f0f1170.png
↑Change it to [DT_TEXT] (not unicode) or [DT_WSTR] (for unicode). To be able to change this, you have to import through "Import Data" and not the "Import Flat file" option.

share|improve this answer

answered 13 mins ago

ChessbrainChessbrain

1436

add a comment | 

0

Rather than using the import wizard, you could place your TSV file on the SQL Server and use OPENROWSET to read the file data directly using a T-SQL script. Using this method and SELECT ... INTO would allow you to create a table based off the actual data types in the file (largest column etc).

See this link for more info.

Basic syntax:

SELECT BulkColumn

FROM OPENROWSET (BULK 'c:tempmytxtfile.txt', SINGLE_CLOB) MyFile 

Check out the documentation for handling things like row terminators etc

share

answered 3 mins ago

HandyDHandyD

939112

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

Paul Kienitz is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f229989%2fcant-import-flat-file-into-sqlserver%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

1

I worked with tsv files before and had a similar issue. When you're importing a flat file, I would suggest you change the way SQL Server interprets the column
https://user-images.githubusercontent.com/35179176/34650736-39eaaab0-f3c6-11e7-9706-377c5f0f1170.png
↑Change it to [DT_TEXT] (not unicode) or [DT_WSTR] (for unicode). To be able to change this, you have to import through "Import Data" and not the "Import Flat file" option.

share|improve this answer

answered 13 mins ago

ChessbrainChessbrain

1436

add a comment | 

1

I worked with tsv files before and had a similar issue. When you're importing a flat file, I would suggest you change the way SQL Server interprets the column
https://user-images.githubusercontent.com/35179176/34650736-39eaaab0-f3c6-11e7-9706-377c5f0f1170.png
↑Change it to [DT_TEXT] (not unicode) or [DT_WSTR] (for unicode). To be able to change this, you have to import through "Import Data" and not the "Import Flat file" option.

share|improve this answer

answered 13 mins ago

ChessbrainChessbrain

1436

add a comment | 

I worked with tsv files before and had a similar issue. When you're importing a flat file, I would suggest you change the way SQL Server interprets the column
https://user-images.githubusercontent.com/35179176/34650736-39eaaab0-f3c6-11e7-9706-377c5f0f1170.png
↑Change it to [DT_TEXT] (not unicode) or [DT_WSTR] (for unicode). To be able to change this, you have to import through "Import Data" and not the "Import Flat file" option.

share|improve this answer

answered 13 mins ago

ChessbrainChessbrain

1436

share|improve this answer

answered 13 mins ago

ChessbrainChessbrain

1436

answered 13 mins ago

ChessbrainChessbrain

1436

answered 13 mins ago

ChessbrainChessbrain

1436

0

Rather than using the import wizard, you could place your TSV file on the SQL Server and use OPENROWSET to read the file data directly using a T-SQL script. Using this method and SELECT ... INTO would allow you to create a table based off the actual data types in the file (largest column etc).

See this link for more info.

Basic syntax:

SELECT BulkColumn

FROM OPENROWSET (BULK 'c:tempmytxtfile.txt', SINGLE_CLOB) MyFile 

Check out the documentation for handling things like row terminators etc

share

answered 3 mins ago

HandyDHandyD

939112

add a comment | 

0

Rather than using the import wizard, you could place your TSV file on the SQL Server and use OPENROWSET to read the file data directly using a T-SQL script. Using this method and SELECT ... INTO would allow you to create a table based off the actual data types in the file (largest column etc).

See this link for more info.

Basic syntax:

SELECT BulkColumn

FROM OPENROWSET (BULK 'c:tempmytxtfile.txt', SINGLE_CLOB) MyFile 

Check out the documentation for handling things like row terminators etc

share

answered 3 mins ago

HandyDHandyD

939112

add a comment | 

Rather than using the import wizard, you could place your TSV file on the SQL Server and use OPENROWSET to read the file data directly using a T-SQL script. Using this method and SELECT ... INTO would allow you to create a table based off the actual data types in the file (largest column etc).

See this link for more info.

Basic syntax:

SELECT BulkColumn

FROM OPENROWSET (BULK 'c:tempmytxtfile.txt', SINGLE_CLOB) MyFile 

Check out the documentation for handling things like row terminators etc

share

answered 3 mins ago

HandyDHandyD

939112

SELECT BulkColumn

FROM OPENROWSET (BULK 'c:tempmytxtfile.txt', SINGLE_CLOB) MyFile 

share

answered 3 mins ago

HandyDHandyD

939112

answered 3 mins ago

HandyDHandyD

939112

answered 3 mins ago

HandyDHandyD

939112