import .txt to sql server

Question

import .txt to sql server

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

Good morning.
I need to import a large amount of information into a table in a flat file .txt and the information is separated by | . Using the statement bulk..insert..with..etc ; it was not possible to import, because the data exceeds the size of rows:

Can not create a row of size 15817 which is greater than the maximum allowable row size of 8060. The statement has been terminated.).

It is possible to make a type of select to the flat file, and the result is to import it into the table I want, since I do not really need all the columns that are in the file.
I appreciate your help, I have already searched a lot and I have not found how to do it.

sql-server

asked by Carlosj 09.07.2018 в 20:09

source

2 answers

unlink and file_put_contents permission denied Android Studio: My activitymain.xml does not recognize string.xml variables

score 1 · Answer 1

You can try to open the file in excel, convert it into columns separated by characters (Data-> Text in columns-> Delimited -> Separators).

Save the file as a new CSV after removing the columns you do not need and then use the SQL Import and Export Wizard to import it.

If the problem is the size of a column you can try this response accepted in MSDN

score 0 · Answer 2

By the SQL Server tool, SQL Import & Export Wizard can do it directly on your TXT file. On the database where you want to have the data, right click and look for the option "Import Data", when the wizard window appears, and select, in the next window, the provider "Flat File Source" as data source and you fill in the information they ask for. Take special care to define the required columns well by means of the column delimiter and also to select the desired columns. When giving next, you select the provider for the data destination and the requested information. By clicking on the following window, you can edit the type of data and other information you require by each required column. When giving the following, the suggestion is to save the SSIS package for later use according to what you indicate, either in SQL Server or in the file system.

With the above, you have some control over the procedure and even, with the saved package, you can execute it manually or in a programmed way by means of a JOB in SQL Server.