How can I insert data into SQL server from a delimited text file having fixed positions

Question

0.00/5 (No votes)

See more:

SQL-Server-2016

I have 390 delimited text files collected from different sources. I have to load them in SQL Server. I don't want to use import and export wizard. I want to create a Bulk Copy Command Mechanishm through which data can be inserted into database.

Every file has 32 columns but there are certain files in which columns are less because of inavailibility of information.
Data collected from every source is different, in-terms of Specifying Spaces between data.
I have searched about this online. The main confusion is in Specifying Starting & Ending Position of a particular column.
How to specify a Field Terminator for a Column. Because space in between the columns is different. I am not able to guess any pattern.
I want to create a table, comprising of following columns:-

Path of the Control file
Column (1) Starting Postion
Column (1) Ending Position
Column (2) Starting position
Column (2) Ending Position
From this table I want to create a dynamic bulk copy command that can copy all data in SQL Server.

Creating this would be really helpful for me. I am not able to see any sort of help for this on Google.

Please refer to me any google link or guide me, How can I do this. Please.

What I have tried:

BULK INSERT TblTable
FROM 'D:\PD_C1\1.txt'
WITH
(
  FIELDTERMINATOR = '','',
  ROWTERMINATOR = ''\n'',
)

Posted 17-Dec-19 0:53am

Hamza Hussain

Updated 17-Dec-19 5:10am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

#realJSOP · Accepted Answer · 2019-12-17T02:44:00

Solution 1

You can write a SSIS package that does it, or find a code snippet that parses it and transforms it into a SQL compatible entity that can be inserted into the database.

Posted 17-Dec-19 2:44am

#realJSOP

AFell2 · Accepted Answer · 2019-12-17T05:11:00

Solution 2

It sounds like you are dealing with fixed length files as opposed to value delimited files (i.e., comma or tab delimited). I assume this because you describe the columns as having a starting position and ending position.

If this is the case, the Almighty Google has a solution for you!
Welcome To TechBrothersIT: SSIS - How to Load Fixed Width Text File to SQL Server Table By Using SSIS Package[^]

Posted 17-Dec-19 5:11am

AFell2

Comments

Hamza Hussain 18-Dec-19 6:53am

Thank you Very Much Sir.
I have understood it. This is exactly where I was stuck. Link is helpful.
Now I have 390 Files.
Should I create mapping of 390 Files in SSIS Package side by side or can I do it programatically ?
After this I want to Call this SSIS package in asp.net code

AFell2 18-Dec-19 17:27pm

Is that 390 files all with distinct column mappings? That would mean you would either have to create 390 SSIS packages, or 1 SSIS package with 390 configurations. I would lean towards the latter, as you can always containerize the configurations to a JSON or xml file and pull them in at processing time to handle the column configuration.