how to read binary file

Need help reading a binary file see below for details...

I have uploaded a csv file into a sql table.

Now i want to extract the data and insert the data in the csv file into another sql table.

What commands can i use in sql to extract/ read the data ?

INSERT INTO {table1}({col1})

SELECT {col2}

FROM {table2}

|||

i'm not reading data from a table....i'm want to read data from a binary file that is stored in a table

|||

Retrieve the value into a string.

Use the string as the buffer of a memory stream.

Use the memory stream as the base stream of whatever type of stream you want to use to actually read the data.

Or

Retrieve the value from the database into a string, and parse it?

Or

Retrieve the value from the database, and save it as a temporay file, then open the temporary file and parse like normal.

|||

Here's how to do the 2nd approach (Which is probably easiest):

dim conn as new sqlconnection(configurationmanager.connectionstrings("ConnectionString").Connectionstring)

dim cmd as new sqlcommand("SELECT MyBlobField FROM MyTable WHEREID=@.ID",conn)

cmd.Parameters.Add("@.ID").Value= {your key here}

conn.open

dim csv as string

csv=cmd.executescalar

conn.close

for each row as string incsv.Split(NewString() {vbCrLf}, StringSplitOptions.RemoveEmptyEntries)

dim cols() as string=row.Split(","c)

' Do insert here by referencing cols(0) - cols(x) for each of the columns in the csv

next

Does that help? Obviously, that's a very simplistic CSV parser, and it doesn't handle embedded cr/lf's, nor does it handle unix/linux written files, nor does it handle quoted fields, or quoted fields with commas in them. You can do a similiar approach using regular expressions to handle more complex CSVs if you need, but I didn't want to over complicate a simple example.

|||

If you are going to go the first or third of my options, you may want to use this to do the actual parsing for you, as it handles most of the known gotchas in CSVs:

http://www.codeproject.com/cs/database/CsvReader.asp

|||

thanks for the info...the info you posted is very helpful but its not exactly what i'm looking for.....i believe i'm not making my problem clear.....

what i'm trying to find out is there a way to read the binary (csv file) in a store procedure and extract the data and insert it into a table.

the csv file contains 2 column of data key and value.

I like to insert each row into a sql table call "TempA" which has colums key and value. I want to do this in a store procedure?

Any ideas?

again thanks for your help

|||

Hi,

As your .csv file is saved as binary data in a database table, I suggest you to pull the .csv file from the datafield first (by BinaryReader) and read the csv file, create the datatable, call your store procedure to insert the table into your database. Here's the sample code for you to transfer your csv data to datatable.

int intColCount = 0;
bool blnFlag = true;
DataTable mydt = new DataTable("myTableName");

DataColumn mydc;
DataRow mydr;

string strpath = ""; //cvs file path

string strline;
string [] aryline;

System.IO.StreamReader mysr = new System.IO.StreamReader(strpath);

while((strline = mysr.ReadLine()) != null)
{
aryline = strline.Split(new char[]{','});

if (blnFlag)
{
blnFlag = false;
intColCount = aryline.Length;
for (int i = 0; i < aryline.Length; i++)
{
mydc = new DataColumn(aryline[i]);
mydt.Columns.Add(mydc);
}
}

mydr = mydt.NewRow();
for (int i = 0; i < intColCount; i++)
{
mydr[i] = aryline[i];
}
mydt.Rows.Add(mydr);
}

Thanks.