Importing XML/ASCII Files To SQl Database Using VB Express
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
ADVERTISEMENT
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
Sep 14, 2006
Hi All,
I need to generate Ascii 7 bit flat file, based on data in db, using integration services, FTP task. Currently i am generating file with ansi-latin and then using the script task converting it to the ascii 7. File looks to be generated properly. But when the target system reads this, they complain that the file has junk charecters some thing like this. when i open it after generating the file it looks fine to me in DOS also. I dont know what is the target system and what OS is used by them. what cud be the issue for these junk charecters and is it possible that a Ascii 7 file generated by windows doesnt work in other OS? If the method i am doing to generate the ascii 7 is not currect then what is the best method for this?
ÿþH^@D^@R^@|^@1^@E^@N^@I^@N^@A^@|^@O^@|^@2^@0^@0^@6^@-^@0^@9^@-^@1^@3^@
PS: earlier i had generated a flat file using data export from Excel & that worked in the target system well. is there any difference in the file encoding generated by excel and integration services?
Please help me!!!!
-vinu
View 1 Replies
View Related
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
View Related
Feb 15, 2006
[Note: originally posted this on microsoft.public.sqlserver.programmingbut had no luck there...sorry if you're seeing this twice]Some facts:1) I have an Access database (.mdb file) sitting on my harddrive.2) I have Visual Studio 2005, Sql Server Express, and Sql ServerManagement Studio Express.3) I do *not* have Microsoft Access.What I'm trying to do:I simply want to import the Access database into Sql Server Express. Inother words, I want to end up with a Sql Server Express database thathas all the same tables, keys, and relationships as the Access databaseas well as all the data from it. I can live without the queries storedin the Access database, but those would be nice too.What I've tried so far:I'm able to connect to the Access database using the "Linked Servers"features in Management Studio Express. From there, I was able to writesome simple Transact-SQL queries to find out what tables are in theAccess database and copy them, one at a time, into a Sql Server Expressdatabase.This is definitely a good start, but it doesn't take care of theprimary keys or foreign keys. There appear to be procedures for thoseas well (sp_primarykeys, sp_foreignkeys), but I keep thinking theremust be an easier way.Which brings me to...Questions:Without having to buy additional software/tools, can I import thisAccess database without a lot of programming? If so, how?Thanks in advance,-Dan
View 1 Replies
View Related
Feb 1, 2006
Is it possible to import an MS-Access database (or table) into a new SQL Server 2005 Express database? If so, how is it done?
View 1 Replies
View Related
Nov 29, 2006
hi, i have an excel spreadsheet with data that i want to place into a sql database.is there any easy way of doing this with sql server 2005 express?
View 3 Replies
View Related
Sep 5, 2007
Hi,Could someone give a pointer how to import couple of hundred imagesinto Sql Server 2005 Express Edition database?Is there a tool for it? Can it be done with Sql Management Studio oris it just a matter of writing own piece of software (a little helperapp) to do it?-timonardo
View 1 Replies
View Related
Nov 5, 2007
Hi all,
I downloaded and ran AdventureWorks.msi into my SQL Server Management Studio Express (SSMSE) one year ago.But I did not know how to attach it to my SSMSE then. Last week, I deleted it from the "Add or Remove" of Control Panel and I downloaded the new AdventureWork.msi and installed it my SSMSE. Today, I tried to use the Database Explorer of VB 2005 Express for the first Stored Procedure programming. I clicked on AdventureWorks.mdf and I got the following error: One or more files do not match the primary of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupt and should be restored from a backup. Cannot open user default database. Login failed. Login failed for user 'CENADe1enxshc'. Log file 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataAdventureWorks_Data_log.ldf' does not match the primary file. It may be from a different database of the log may have been rebuilt previously. Please help and advise me how to correct this problem.
Thanks,
Scott Chang
View 9 Replies
View Related
Mar 24, 2008
When I develop I sometimes forget and try to open the database file with the MS SQL Express Manager even though the web application I am devloping is still using it ... error lock occurrs and thats fine .... but what is the best way to unlock the files again? .... often I find myself having to restart the workstation because of this and thats sounds a bit like overdoing it .... there should be an easier way to unlock locked db files ...without having to restart the workstations.... is there ?
/Johan
View 3 Replies
View Related
Jan 18, 2008
I've written a ClickOnce app that deploys a Sql Server Express data file (.mdf) and log file (.ldf) along with the other application files. SQL Express SP2 is a prerequisite for this app. After the applicaiton installs and starts up it copies these files to a specific folder and attaches them by calling the AttachDatabase() method of a Microsoft.SqlServer.Management.Smo.Server object.
This method works fine using Windows XP, however when I run it on Windows Vista I get the following exception:
Microsoft.SqlServer.Management.Smo.FailedOperationException: Attach database failed for Server 'localhostSQLEXPRESS'. ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: Unable to open the physical file "C:WashMasterDatabasewasshmaster.mdf". Operating system error 5: "5(Access is denied.)".
This exception occurs when I try to programmatically attach the database.
I've searched through the MSDN forums for a solution and I've only found two suggestions:
1.) Create all database connections using a User Instance.
2.) Create the database (using SQL) in the target folder, rather than copying it over from the deployment folder.
I don't like the idea of using User Instances because my entire data access layer is currently written using Ole and I would rather not rewrite it if I don't have to. I cannot find any way to use User Instances with Ole.
I also don't like the idea of creating the database using SQL because it is fairly large.
Here are my questions:
1.) Can User Instances be used with Ole?
2.) Can I somehow programmatically set the permissions of the copied data and log files so that SQL Express can access them without error?
3.) Is there a better method for doing what I'm trying to do or am I destined to have to either rewrite in data access layer so I can use User Instances or create my large database rather than copy it to the target directory?
Thanks.
View 4 Replies
View Related
Feb 27, 2015
1) What is the current version of SQL Server Express?
2) HOw much SQL Server Express costs (figure about 500 branch servers)
3)Are there any Tools from Microsoft to convert Access 97 directly to SQL Server express, and how much do they cost?
4)Server Hardware requirements to run SQL Server Express - disk size, memory size, security settings, pre-requisite, service needed.
5)Maximum capacity / capabilities of SQL Server Express-max # of simultaneous users,tables,rows,database size.
6)Any installation instruction for SQL Server Express.
View 4 Replies
View Related
Oct 22, 2015
I’m getting ASCII characters in one column of my table. So I want to replace same column value in NON ASCII characters.
Note – values in column must be same
View 10 Replies
View Related
Aug 22, 2006
Hi everyone,
I need to save the ascii data in my sql express 2005 database in one table and in 2 coloums in this table using VB express...
Can I do it by datareader or with bulk insert t-sql command?
I tried it this way but it gives error like "connection property is not initialised" it is on executenonquery row in the code...
Dim cmd = New SqlCommand("BULK INSERT RFID.dbo.stock(material_number,total) FROM 'C:can1.txt' " & _
"WITH FIELDTERMINATOR = ',', ROWTERMINATOR = '' ")
connection.Open()
cmd.executenonquery()
connection.Close()
I hope some can help with this problem....
View 6 Replies
View Related
Mar 1, 2006
Anyone know of a good "free" way to back up web files and SQL Server 2005 Express Database?
I was able to use Windows Server 2003 Backup utility to back up the folder where the Databases were stored, as well as the web files, with no errors.
But I have heard a lot of discussion that you can't just simply backup SQL Server data files?
I'm wondering how sound the backup I've created is...
Any suggestions?
View 1 Replies
View Related
Nov 19, 2006
Hi,I am new to SQL express and try to solve the 4GB size limitation.Is there a possibility to create a new database file every time I getto the limit?How can I do that with C#? how can I create new database file everytime it gets full?Can I be connected to two database files at the same time (the full dband the new db)?thanks in advance,oren
View 1 Replies
View Related
Oct 6, 2006
Adedoyin Akinnurun writes "i have a database that is running using regular ascii characters.. i am trying to migrate this database to support several other languges(globalization). I need ideas on how to migrate this database to UTF 8 .
Someone suggested converting all the varchar and char to nvarchar and nchar .. but i have a lot of data on the system and this might take a lot of time..
Any ideas would be appreciated !!!!"
View 1 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
Jan 29, 2008
I need to write a genric CSV importer. one set of CSVs define the formats of all teh other CSVs. The format of the second set of CSVs is not know at the time of creating this project/SSIS package.
Imagine having two sets of CSV files.
Each file in the first set of CSVs define the format of files in the second set of CSVs
The first set always have the same format. Each row of the CSV file defines a field with a Name and Type.
The first two columns of each row of in the first set of CSVs have a FormatName and index.
So a simple file may have:
Format1,1,Name,string
Format1,2,Surname,string
Format1,3,DOB,Date
Format1,4,Email,string
The second set of CSVs contains records that have to comply with the format define in the first set.
So in this aprticular case, a CSV file in the second set may have records such as:
Format1,John,Doe,2007-01-01,john@doe.com
Format1,Jane,Doe,2007-02-02,jane@doe.com
The problem I have is creating a generic SSIS package for this.
The first task, loading the first set of CSV file is fairly simple. The CSV format is known.
But the second task is a bit trickier.
Assuming I have SQL tables to load the data.
One 'Fields' record for each row in the CSVs from the fiirst set.
One 'Rows' record for each row in the CSVs from the second set,
One 'Values' record for each value in each row in the CSVs from the second set,
Something like:
TABLE Fields (
FieldID int IDENTITY,
FormatName varchar(100),
Position int,
ColumnName varchar(100),
ColumnType varchar(20) )
TABLE Rows (
RowID int IDENTITY )
TABLE Values (
ValueID IDENTITY,
RowID int,
FieldID int,
ColumnValue varchar(100) )
What would be the best/easiest SSIS approach:
- Dynamically create a SSIS package based on the content of each CSV fiel of the first set and execute that package for each file in the second set, selecting the correct package to execute (all records within a CSV file belong to the same format).
- Write a single SSIS package that iterates through the rows of the second CSVs, does a lookup for each value of each row to find the field name and make an insert into the DB
- Other SSIS method?
- Don't use SSIS to parse the CSV and call a custom C# task?
- Don't use SSIS at all ?
Thanks
View 1 Replies
View Related
