How to Bulk Inserts via TSQL without using FIELDTERMINATOR

Question

0.00/5 (No votes)

See more:

SQL-server-2000

XML

Hi, 
I want to bulk insert data from .dat file.
but the problem is the file doesn't contains any char by which i could separate different values..
Actually the file contain code generated by Attendance machine, the code looks like 
**31201201100915000100000043210001**
**31201205301806000200000043210011** 

Above 2 lines are Attendance of 1 day of Employee **4321**, details are below 

31 - Machine Code
2012 - Year  
01 - Month 
10 - Day 
09 - Hour 
15 - Min 
0001 - In or Out (0001 for In & 0002 for Out) 
0000000061 - Employeecode 
0001 - Terminal No (0001 for Terminal In & 0011 for Terminal Out) 

can i bulk import this file ? if yes then how? can anyone tell how i can solve this problem?

Thanks

I'm using SQL server 2000 :(

Posted 12-Dec-12 1:55am

zainknoman

Add a Solution

1 solution

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)

__TR__ · Answer 1 · 2012-12-12T02:31:00

You can insert the machine generated code into a column in a staging table and then use the SUBSTRING[^] function to retrieve the data from that column.
Here is a code sample

SQL

CREATE TABLE tblSample
(
	Col1 VARCHAR(MAX)
)

Your bulk insert would look like this

SQL

BULK INSERT tblSample
FROM 'Your file path'
WITH
(
    ROWTERMINATOR = '\n'
)

Sample code of how to use substring function to split the data in single column.

SQL

INSERT INTO tblSample
SELECT '**31201201100915000100000043210001**' UNION ALL
SELECT '**31201205301806000200000043210011**'

SELECT SUBSTRING(Col1, 3,2) AS MachineCode
    ,SUBSTRING(Col1,5,4) AS [Year]
    ,SUBSTRING(Col1,9,2) AS [Month]
    ,SUBSTRING(Col1,11,2) AS [Day]
    ,SUBSTRING(Col1,13,2) AS [Hour]
    ,SUBSTRING(Col1,15,2) AS [Min]
    ,CASE WHEN SUBSTRING(Col1,17,4) = '0001' THEN 'In' WHEN SUBSTRING(Col1,17,4) = '0002' THEN 'Out' ELSE SUBSTRING(Col1,17,4) END AS [InOrOut]
    ,SUBSTRING(Col1,21,10) AS EmployeeCode
    ,CASE WHEN SUBSTRING(Col1,31,4) = '0001' THEN 'Terminal In' WHEN SUBSTRING(Col1,31,4) = '0011' THEN 'Terminal Out' ELSE SUBSTRING(Col1,31,4) END AS [TerminalInOrOut]
FROM tblSample