Solved

SQL: How do I bulk insert this data?

Posted on 2011-03-09
2
241 Views
Last Modified: 2012-06-21
I'm new to MS-SQL.  I'm familiar with bulk-inserting from a CSV, however I have a set of data in text files that is not delimited the way a CSV would be and I need to get this into a SQL database.

The data is in a text file, contained in 4-line sets (so each row is four lines), for example:

@EA-GAII-02_0000:7:1:6770:966#0/2
CTAGCTAGCTAGGGTGCTGATCGATCAGCTAGCTGATCGAGTCGATCGATCGATCGATCGA
@EA-GAII-02_0000:7:1:6770:966#0/2
hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhghhhhhhhhhhhhhhhhhhhhhhhhhhhhhggggghhhh

I need to import this data into columns as follows:

Column Name - Data
-------------------------
Cell_No - 7
Tile_No - 1
X_Coord - 6770 [the length of this number could be 1-10 digits]
Y_Coord - 966 [the length of this number could be 1-10 digits]
Pair - 2
Sequence - [all of line 2]

The rest of the information is unimportant.

Essentially, I need to extract bits of data from line 1 and place into multuple columns, place all of line 2 in its own column and ignore lines 3 and 4.  I'm not sure how to write the bulk insert sql to do this.  Can anyone provide an example from a similar import that they have done to get me started?
0
Comment
Question by:I_play_with_DNA
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 9

Expert Comment

by:AriMc
ID: 35088743
I'd go for a simple C-program to tokenize the text. Do you have access and know how to use a C-compiler?

0
 
LVL 7

Accepted Solution

by:
tlovie earned 500 total points
ID: 35088919
I think the problem is too specific to provide a simple recipe that would work, but here's what I would do if presented with this problem

Make a table that has 2 columns
create table tblData (LineNo int identity(1,1), Data varchar(200) )
truncate table tblData

bulk insert tblData with a format file this link will help you do that http://www.developerfusion.com/code/5357/bulk-insert-from-flat-file-using-transactsql/

Make your format file such that all your data lands in column 2 of the table, and has line numbers attached to it.

Now, you've got your data into a table, with row numbers, hopefully in the correct order that it came in as.

Then I'd write a join like this:
select *
from tblData a inner join tblData b on a.LineNo=b.LineNo-1
where (a.LineNo%4)=1
-- this will give you every fourth line, joined to the corresponding line 2

Then you'd need to write some substring expressions that use the charindex function:
substring(a.Data, charindex(a.Data, ':', 0))
-- these charindex expressions are going to get ugly in a hurry, because the 3rd argument to it will be the starting position of where you want to search from.  Which from your explanation will depend on charindexes of previous statements.  

If you attach a sample data file here, I will attempt to help you.

Good luck.


0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question