Importing Binary Files Into Sql Server Database
Sep 26, 2005
I work for a company that makes heat transfers for the imprinted apparel market. We're developing a database of merchandise images for all of our non-design inventory. Using Access we're going to be inserting thumbnails of psd (photoshop) files. We're wondering if there is any way to import multiple psd's into the sql server database into matching records like matching a column named "filename" and the actual filename of the file without having to upload each file individually. We want to be able to dump the files from the database of the matching records, also. This way, once our catalog designer has found which designs they need to put into the new catalog, it will dump the psd's for us. The same for our staffer who does color separations.
Any suggestions out there? If you need me to post further of what we're trying here, I will. This is for the bossman.
View 4 Replies
ADVERTISEMENT
Apr 24, 2008
Hi.
There is a "text" file generated by mainframe and it has to be uploaded to SQL Server. I've reproduced the situation with smaller sample. Let the file look like following:
A17 123.17 first row
BB29 493.19 second
ZZ3 18947.1 third row is longer
And in hex format:
00: 41 31 37 20 20 20 20 20 ”‚ 31 32 33 2E 31 37 20 20 A17 123.17
10: 66 69 72 73 74 20 72 6F ”‚ 77 0D 0A 42 42 32 39 20 first row™ª—™BB29
20: 20 00 20 34 39 33 2E 31 ”‚ 39 20 20 73 65 63 6F 6E 493.19 secon
30: 64 0D 0A 5A 5A 33 20 20 ”‚ 20 20 20 31 38 39 34 37 d™ª—™ZZ3 18947
40: 2E 31 20 74 68 69 72 64 ”‚ 20 72 6F 77 00 69 73 20 .1 third row is
50: 6C 6F 6E 67 65 72 ”‚ longer
I wrote "text" in quotes because sctrictly it is not pure text file - non-text binary zeros (0x00) happen sometimes instead of spaces (0x20).
The table is:
CREATE TABLE eng (
src varchar (512)
)
When i upload this file into SQL2000 using DTS or Import wizard, the table contains:
select src, substring(src,9,8), len(src) from eng
< src ><substr> <len>
A17 123.17 first row 123.17 25
BB29 493.19 22
ZZ3 18947.1 third row 18947.1 35
As one can see, everything was imported, including binary zeros. And though SELECT * in SSMS truncates strings upon reaching 0x00's, still all information is stored in tables - SUBSTRINGs show that.
When i upload this file into SQL2005 using SSIS or Import wizard the result is following:
< src ><substr> <len>
A17 123.17 first row 123.17 25
BB29 4
ZZ3 18947.1 third row 18947.1 25
This time table is half-empty - all characters behind binary zeros in respective rows are lost.
I stumbled upon this problem while migrating my DTSes to SSIS packages. Do you think there is some workaround, or i need to turn on some checkbox or smth else could help? Please...
View 8 Replies
View Related
Aug 1, 2006
i have a table with rows of file names and paths. what i'm trying to do is process each file and store it in my sql database. i want to store the files as binary files (they are word and excel and pdf files) anyone know a way to do this? it would especially be useful if i could do this with a console application so i can schedule it
View 2 Replies
View Related
Jul 19, 2006
Hi everyone,
I have to write a program in VB to receive the read data from a RFID reader for my graduation project.The problem is I am not a computer science student so I have only general info on programming.
I created my DB in VB express but I couldn't find out how to send the read data (that will be either in XML or ASCII format) to my database...The read data will be transferred to my computer by the RFID reader's software but after that I don'T know how to transfer it to my DB.As I know I have to use commands like read.xml etc,but no idea how write the complete program.
I checked the forum and couldn't find the answer,sorry if someone already answered my question and I missed it.
Thanks...
Can
View 2 Replies
View Related
Sep 20, 2004
Hi,
Can anyone help? Need to upload a text file to a sql database but keep getting errors.
I'm creating a page that will allow users to to bulk import and update to a MsSql database. The users provide a text file every so often with new/update information. So i want to use a DTS package to transform the infomation, and create a table in the database, then check against existing/non existing records, if the record exist, update it, if not insert it. I'm using Visual Studio.Net, ASP.Net and coding in VB.Net.
Anyone know where i can find documentation/code regarding the above?
I will be greatful for any help.
View 5 Replies
View Related
Jul 23, 2005
HI!I am importing .txt files. How can i check the errors? I have created alog file, but the problem is that i lose some characters.I import for example:CodeABCFZHJHNfrom a text file, but sometimes Code can be 4 caracters longI import this 3 characters long now. When i add the same structuredtext file with some rows lenght 4, it skips the last character, but iget nothing in the log file.please helpxgirl
View 4 Replies
View Related
Nov 22, 2007
Hello,
I receive 4 .csv file downloads periodically (3 times per day) via email from our corporate database. I open each file in Excel, save as excel files, import to Access, replace the previous tables...run action queries, generate reports combined with production data from CSRs and supervisors. ALL DONE MANUALLY!!!
Here's where I am now:
-I've recently switched over to SQL Server Express.
-Used SSMA to bring tables over from previous Access database.
-Exercised the option to LINK these tables to the original Access database.(I'll explain why in a moment)
-Created ADPs for front end data entry use.
-Imported old Access database forms into the new ADPs previously used in Access.
-Connected to the new Server Express.
-I've eliminated my concurrent user problems by doing this.
HOWEVER, I am still bound to using old Access/Jet database to generate reports based on periodic downloads from corporate .csv files.
Here's the question:
What is the best way to import the csv files being sent to me via email into SQL Server Express? I've tried DTS. Seems to me you can't save AND actually use the packages later since it's the Express edition..... Importing manually 4 files, 3 times per day is a very tedious option I'd like to avoid. Any ideas?
Oh, by the way. Corporate has told me they would be willing to post the files to an FTP site instead of emailing the files. That's about as much help as I'm going to get from them. Can SQL Server Express be set up to run stored procedures (triggers) on a hot folder?
Thanks for your help.
David
View 3 Replies
View Related
Mar 22, 2007
Hi all,
Sorry if this is in the wrong forum but i didn't know if i should put it in the Data Mining forum.
My problem is two fold.
Scenario:
I have 11 text files with standard data format. They range in size from 20megs to 300+ megs (containing 100's of thousands of lines of data)
I build a simple DTS Package to automate the importing of the text files into a database table with the same format as the text files.
Running the files through my DTS produced errors on all files. The error was :
Too many columns found in the current row; non-whitespace characters were found after the last defined column's data.
The error also gave an approximate position of the problem row. So i opened up a text editor that can handle large files and took out about 5000 records before and after the problem area. So i now had a new test file that had 5000 records and contained what "should" be the problem row. I ran this file and it imported correctly. It did not produce the error that i got previously. However it did import things incorrectly. Going through only one of the files i found several rows (over two dozen) that had half the data on one row, then the other half on the other row. Obviouslly there is a problem here with the way the data was exported. I wanted to know if anyone knows of any programs i can get that will analyze these text files for inconsistencies. I have considered writing one in C# but want to see if there is something already out there that can help me.
Basically i need to look at these text files and find Rows that are "incomplete"
the files come from a unix based system, use LF for row delims and Chr(29) for Column delimiters.
any ideas?
thanks,
mcm
View 4 Replies
View Related
May 12, 2007
Greetings,
I'm having a tough time importing some of my legacy database into sql.
I have a number of dbase (IV) files I need to get into SQL. I have tried building a SSIS package with either an foxpro oledb connection or a jet 4.0 one, none of them work bec. of inconsistencies in the data format in my tables (e.g. date fields, etc).
I have tried to save the .dbfs as excel 2007 files, taking advantage of the larger space that comes with '07. Problem is you can't use the import/export wizard with 2007 for some reason and I haven't been able to create a package with the access 12 oledb as I have read.
I have to get some crucial data out of that old system and into the new one and I can't seem to be able to import them properly.
Any hints on what I should do ? (maybe I'm doing something awfully wrong)
Thank you for taking the time to answer my question,
Val
View 12 Replies
View Related
Nov 2, 2007
Hello, I have a table that stores binary files. When I serve them up to the user, I call the following page (serveDocument.aspx?DocumentID=xxx) which holds only the following code and pass the document ID: Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Dim DocumentID As Integer = Convert.ToInt32(Request.QueryString("DocumentID")) 'Connect to the database and bring back the image contents & MIME type for the specified picture
Using myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("kelly_lmConnectionString1").ConnectionString) Const SQL As String = "SELECT [Content_Type], [Document_Data] FROM [lm_Service_Detail_Documents] WHERE [Document_id] = @Document_id"
Dim myCommand As New SqlCommand(SQL, myConnection) myCommand.Parameters.AddWithValue("@Document_id", DocumentID) myConnection.Open() Dim myReader As SqlDataReader = myCommand.ExecuteReader If myReader.Read Then
Response.ContentType = myReader("Content_Type").ToString() Response.BinaryWrite(myReader("Document_Data")) End If
myReader.Close()
myConnection.Close()
End Using End Sub It works perfectly. But heres the thing: I want users to be able to right click on the link and chose 'Save Target As'. In IE 7, this works fine. In FireFox it saves the document as 'serveDocument.aspx'. Is there any way to inject the filename that I want to save the document as? Thank you.
View 5 Replies
View Related
Apr 16, 2006
Is there any way to store a binary or image file into a database record / save a binary or image record back into a file using Transact SQL? If yes, would the "image" data type be most suitable?
(As a background, I am working on a process that deals with incomign emails, and I am using SQL Mail for that purpose. The above is needed to deal with attachments that may come with emails, and which are saved in a physical file on the HDD).
Thanks a lot in advance...
View 3 Replies
View Related
Jul 23, 2005
Hi folks,Is it possible to store Binary Files in MS SQL 2000 ??? Say I have a100K PDF or a 150K word document. Is it possible to store this in afield in MS SQL and pull it out somehow? We're using ColdFusion on theserver on Apache.Thanks,Ringo
View 3 Replies
View Related
Nov 29, 2006
Hi There,
I am new to VC# and SQL Express. I am currently trying to find a way to store user selected files into SQL Express. I am using a column with the varbinary(MAX) data type. My current thought is to give the user a open file dialog box, and let them select the file they want to upload. On closing this dialog box, I convert the file selected into a file stream and insert into SQL Express.
Is this method a good one? If anyone else can give me pointers or hints to better methods, please do!
Thanks,
Ke
View 4 Replies
View Related
Dec 11, 2007
Hey everyone,
I'm deploying a desktop application with Sql CE 3.5. I have a collection of files that I would like to be save as a binary format in SQL CE. These files range from 1KB to 5MB. I know I cannot use varbinary(max) , instead I am limited to varbinary(8000), but this obvisously comes short of 5MB. There are articles stating to use the image datatype to get around this. Is this just bad practice, should I keep the files on the file system or take advantage of this work around? Do you have any suggestions?
Thanks in advance,
-- Chris
View 10 Replies
View Related
Feb 16, 2004
i need to store a bmp or zipped file in a field of a ms sql db. i read the file using vb6 o.net, and in my mind i think sto store it in binary mode. the files could be more of 12 mega. whitch kind of field a could use?
View 1 Replies
View Related
Jul 11, 2006
Hi
When images are uploaded and stored directly into a sql database as binary data (eg in the club starter kit) how can those images be accessed and displayed.
When I open the images table in VWD and select display data, the cells holding the image data hold a <binary data> tag. What I want to be able to do is get at that data, or actually get at the image so that it is displayed. My reason is this, at the moment the only way to access the images in the sql database after they have been uploaded is to log into the website and view them as an administrator of the site. It would be much simpler if I could access the database directly and view the contents of the images table.
Any ideas?
Thanks
View 2 Replies
View Related
Apr 29, 2015
We run std 2008 r2, I need to recreate flat files from their varbinary(max) equivalents in our db. I have a mix of excel, pdf, word etc to recreate. Will ssis be a good tool for doing this?  I'm wondering what transform(s) would be involved.Â
Perhaps I need to cast to varchar 1st and then land the data but if I recall correctly there is a maximum record length in ssis destination flat file rows. And I'm thinking I would have to map the varbinary (or cast equiv) to a row in the destination once for each file created.  Â
View 4 Replies
View Related
Jun 2, 2015
I have a well-structured but also very large binary data-set that is generated by a C++ application every five minutes. The data needs to be accessed by SQL applications. Since data is generated every five minutes, performance is key, both for write and read. The data set is about 500MB.If data is written to the file system, the write performance doesn't involve SQL server. For reading it, I have a CLR to read the portions of the data that I need based on offset and length. That works and is very fast. The problem is that data is stored in the file system, so it is not self-contained within the database.
A second option that I haven't explored yet, is to write the data into a table as VARBINARY(MAX). I would read the data using SUBSTRING with appropriate offset and length. Performance of SQL write/read of binary data of this size, and whether there is a third option I haven't thought off. I'm using SQL Server 2014.
View 5 Replies
View Related
Feb 16, 2007
With DTS it was easy to import a DBF file, but its a huge pain with SSIS. Is there any way on the horizon to import DBF files as easily as DTS did?
View 1 Replies
View Related
Jul 23, 2005
Hello,I need to import a bunch of .csv files. The problem I am having is the"non data" information in the files creating bogus rows and columndefinitions. Here is an example of the csv file.CBOT - End-of-Day Futures Bulk Download 2001.2 Year U.S. Treasury Notes FuturesDateSymbolMonth CodeYear CodeOpen20010103ZTH2001102.0937520010104ZTH2001102.0312520010105ZTH2001102.28125In this case, there are bogues rows created with the text at thebeginning of the file, and also the column names get placed into a rowas well. My question is; how do you import the file and strip out the"non-data" data? so that only the actual data gets inserted into the db?Thanks,TGru*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 4 Replies
View Related
Jul 20, 2005
What is the best way to import a structured .prt export file from anaccounting package into SQl Server??Tabs, Fixed col length???
View 1 Replies
View Related
Sep 14, 2006
I have the original copies of the .MDF and .LBF of another database that has a bunch of data in that I'm trying to important into MSSQL Express for a project I'm working on. I no longer have a running version of this database just the .MDF and .LBF files and I have no clue what version of MSSQL they were originally created with. If anyone knows a way to import this database please help.
View 1 Replies
View Related
May 2, 2007
I am trying to import a CSV file into an SQL Server table with the OleDbDataReader and SqlBulkCopy objects, like this: using (OleDbConnection dconn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\mystuff\;Extended Properties="text;HDR=No;FMT=Delimited""))
{
using (OleDbCommand dcmd = new OleDbCommand("select * from mytable.csv", dconn))
{
try
{
dconn.Open();
using (OleDbDataReader dreader = dcmd.ExecuteReader())
{
try
{
using (SqlConnection dconn2 = new SqlConnection(@"data source=MyDBServer;initial catalog=MyDB;user id=mydbid;password=mydbpwd"))
{
using (SqlBulkCopy bc = new SqlBulkCopy(dconn2))
{
try
{
dconn2.Open();
bc.DestinationTableName = "dbo.mytable";
bc.WriteToServer(dreader);
}
finally
{
dconn2.Close();
}
}
}
}
finally
{
dreader.Close();
}
}
}
finally
{
dconn.Close();
}
}
}
A couple of the columns for the destination table use a bit datatype. The CSV files uses the strings "1" and "0" to represent these.When I run this code, it throws this exception:Unhandled Exception: System.InvalidOperationException: The given value of type String from the data source cannot be converted to type bit of the specified target column. ---> System.FormatException: Failed to convert parameter value from a String to a Boolean. ---> System.FormatException: String was not recognized asa valid Boolean. at System.Boolean.Parse(String value) at System.String.System.IConvertible.ToBoolean(IFormatProvider provider) at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider) at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType) --- End of inner exception stack trace --- at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType) at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaDatametadata) --- End of inner exception stack trace --- at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaDatametadata) at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal() at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount) at System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader) at MyClass.Main()It appears not to accept "1" and "0" as valid strings to convert to booleans. The System.Convert.ToBoolean method appears to work the same way. Is there any way to change this behavior? I discovered if you change the "1" to "true" and "0" to "false" in the CSV file it will accept them.
View 3 Replies
View Related
Sep 14, 2007
Hi,
I got one more problem with importing csv files using .net. The problem is that the csv file contains double-quotation marks (""). For example, the record looks like:
...,Bearing Double "D" Flange,...
And the result is: ... | Bearing Double | null (all following columns are null)
The code is as following:
string strCsvConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=;Extended Properties='text;HDR=Yes;FMT=Delimited(,)';";
using (OleDbConnection cn = new OleDbConnection(strCsvConn))
{
string strSQL = "SELECT * FROM " + strFileName;
OleDbCommand cmd = new OleDbCommand(strSQL, cn);
cn.Open();
using (OleDbDataReader dr = cmd.ExecuteReader())
{while (dr.Read())
{
string str = Convert.ToString(dr[8]);
}
// Bulk Copy to SQL Server
//using (SqlBulkCopy bulkCopy = new SqlBulkCopy(strSqlConn))
//{
// bulkCopy.DestinationTableName = strSqlTable;
// bulkCopy.WriteToServer(dr);
//}
}
}
Any idea is highly appreciated.
shz
View 2 Replies
View Related
Apr 10, 2001
Hi,
I want to import multiple text files into a single table. I know I have to use BCP or DTS. But, I want import all files at once, instead of one at a time. And the file names are in sequence, viz. file1, file2, file3 etc.
Can anybody tell me, How I can achieve this.
Thanks
View 4 Replies
View Related
Jul 7, 2000
Has anyone written a SQL script to import ASCII comma or space delimited?
I am trying to load a PROGRESS exported file (Unix) into SQL SERVER 6.5 (NT).
Much appreciated,
Nickd
View 1 Replies
View Related
Sep 19, 2001
I have to import 18000 text files into a sql database.
Each file contains 10 fields and around 5000 records.
I am currently doing this with DTS.
What I am wondering is this: Is DTS the most efficient i.e. quickest way to import all this data. Bearing in mind there is about 90 million records to import in all.
I would appreciate the benefit of somebody elses experience when dealing with this type of thing.
Cheers,
Brookesy
View 2 Replies
View Related
May 10, 2004
Hello,
I've been trying to import a TXT file into an SQL database and I'm having trouble making it work correctly. It is a ASCII text file with over 100,000 records. The fields vary by the number of characters. This can be 2 characters up to 40 (STATE would be 2 characters, CITY is 32 characters, etc.)
I can import the file with DTS. I go in and select exactly where I want the field breaks to be. Then it imports everything as Characters with column headers of Col001, Col002, Col003, etc. My problem is that I don't want everything as Characters or Col001 etc. I want different column names and columns of data to be INT, NUMERIC(x,x), etc. instead of characters every time. If I change these values to anything than the default in DTS it won't import the data correctly.
Also, I have an SQL script that I wrote for a table where I can create the field lengths, data type etc. the way I want it to look, FWIW. This seems to be going nowhere fast.
What am I doing wrong? Should I be using something else than DTS?
Any suggestions are greatly appreciated.
Thanks,
JB
View 5 Replies
View Related
Apr 25, 2008
Hi i've 6 files with the same name part but all have a different ext. The 17 always changes to the current week number
xbouns.A17
xbouns.B17
xbouns.C17
I want to import these files into a database table. So I've create a foreach loop and select the foreach file enumerater but am not sure if this is the right way to go about it some help woth be great thanks.
View 7 Replies
View Related
Aug 5, 2005
I have over three hundred text files that I need to import to SQLServer.Each is in the exact same format.I want to import tham as seperate tables.Is there any way to do it in one process?Regards,Ciarán
View 5 Replies
View Related
Apr 28, 2008
Hi i've 6 files with the same name part but all have a different ext. The 17 always changes to the current week number
xbouns.A17
xbouns.B17
xbouns.C17
I'd like to do this within a SSIS package some help in getting this to work would be great.
Thanks
View 12 Replies
View Related
Jan 24, 2008
Hello Everyone.
I am a bit new to SQL Server but not to DBA or programming per se. I am having difficulties getting either an Excel or Text flat file to import properly.
I guess it would be best to ask, using either SSIS or BULK INSERT, what options need to be entered for a typical excel flat file?
View 2 Replies
View Related
Feb 28, 2006
I have a package with a corresponding configuration file.
If I open Sql server management studio and i connect to SSIS and right click to import a package, and then select to store the package in SQL Server (not on the file system)....
What happens with the configuration file?
Does the import take the values from the configuration file and place them in the package which then is stored in SQL Server?
Or do I need to put the configuration file someplace on the SQL Server where the package is imported so it can access it when it runs?
I'm a bit confused about what goes on there?
For example, I tried using the build command and then running the manifest file to import using the wizard and when it does that it copies the configuration files to a default location within the c:program filesmicrosoft sql server directory.
Thx.
R-
View 1 Replies
View Related
