Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

SQL: How do I bulk insert this data?

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
I_play_with_DNA
Asked:
I_play_with_DNA
1 Solution
 
AriMcCommented:
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
 
tlovieCommented:
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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now