Importing Access DB: writing

Showing posts with label writing. Show all posts

Wednesday, March 28, 2012

Importing text from a flat text file.

I have a DOS scripts that echo's some PC data to a local log file but instea
d
of writing directly to this log file i'd like the output of this script to
populate an SQL 2000 database. How would i go about redirecting the output
from a flat text file to a SQL database.
Regards
John>> How would i go about redirecting the output from a flat text file to a
Some options include Bulk Insert, BCP IN or DTS. Simply search the index in
SQL Server Books Online & you'll find all the information you need to use
them. Also, for DTS, check out www.sqldts.com
Anith|||"ValleyBoy" <ValleyBoy@.discussions.microsoft.com> wrote in message
news:D54A35D3-BE24-458A-B371-27B0A239B043@.microsoft.com...
> I have a DOS scripts that echo's some PC data to a local log file but
instead
> of writing directly to this log file i'd like the output of this script to
> populate an SQL 2000 database. How would i go about redirecting the output
> from a flat text file to a SQL database.
> Regards
> John
Take a look at bcp, BULK INSERT and osql. I'm not sure which approach will
work with your given situation, or if it's even possible to have it
redirected. You may have to dump it to the flat file and then bcp or BULK
INSERT the data in to SQL Server as part of another batch.
Another possible option is to have DTS run the DOS jobs and do something
with the input. I'm not a DTS expert, so I can't say whether or not this
approach is feasible either.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||for this you might check DTS available in BOL
alternatively check for osql command in BOL
eg:
osql /U alma /P /i stores.qry
or u can use bulk Insert
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"ValleyBoy" wrote:

> I have a DOS scripts that echo's some PC data to a local log file but inst
ead
> of writing directly to this log file i'd like the output of this script to
> populate an SQL 2000 database. How would i go about redirecting the output
> from a flat text file to a SQL database.
> Regards
> John

Monday, March 19, 2012

Importing Excel file to SQL Server (Opinions please)

Dear All,

I am writing a procedure to importdaily the customer excel file to SQL server 2000, I managed to do that where the excel file will be imported directly to the SQL server after creating the new data table, & then I need to read the created table & import it row by row to my original data table.
The problem:

I. The original excel file has the following:
a. a protection password
b. The contents has two merged headers (which effecting the import procedure)
c. And last line is a totals line

Before importing the file I have manually to remove (a – b & c)!!

The Solution:

II. I am trying to find a way to do the above points automatically inside the project.

III. Also I thought of importing the excel file to a DataGrid first then:
a. Let the user approve the file contents &
b. Remove manually point (I.b.) above (I don't now how yet, need to try it).
c. Then import the DataGrid the the SQL server.

I think I prefer solution (III), any suggestions are highly appreciated

Try this thread and read my post there is code and a link to all you will need including free Video tutorials from Microsoft. Hope this helps

http://forums.asp.net/928520/ShowPost.aspx

|||

Dear Caddre,

Thank you for your reply,
I have checked the posted links. they are talking about exporting to excel from sql server/browser.
this not what I want.

Anyhow thanks again.

|||

Hi again,
I desided to preview the excel sheet in a datagrid when the user selects the file, worked fine.
but still I am stuck if the excel file has a password, I tried to do the following :

oOLEDBConn =New OleDb.OleDbConnection
oOLEDBDA = New OleDbDataAdapter("SELECT * FROM " & sDataSheet & " ", oOLEDBConn)
oDS =New DataSet
oOLEDBConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sFilePath & sFileName & ";Extended Properties=Excel 8.0;Password='123';"
oOLEDBConn.Open()
oOLEDBDA.Fill(oDS, "Sheet")

In this case i am getting the following error whether the file has a password or not:
(Cannot start your application. the workgroup information file is missing or opened exclusively by another user)

Any suggestions please.

Friday, March 9, 2012

Importing data into MS SQL 2000 from XML file

Hi Everone,

I'm writing a script to import data from an XML file, and this tutorial is
really an awesome guide :
http://www.kbalertz.com/Q316005/Imp...Component.aspx ... problem
though is here's a snippet of my XML file:

<planets>
<sun rise="6:23" set="20:33"/>
<moon rise="18:54" set="4:26"/>
<mercury rise="7:50" set="22:11"/>
<venus rise="4:24" set="17:38"/>
<mars rise="9:45" set="23:40"/>
<jupiter rise="17:23" set="4:20"/>
<saturn rise="10:09" set="23:53"/>
<uranus rise="1:38" set="13:10"/>
<neptune rise="0:23" set="11:13"/>
<pluto rise="20:38" set="7:25"/>
</planets
<moon>
<phase date="7/6/2006" text="Waxing Gibbous">9</phase>
<phase date="7/7/2006" text="Waxing Gibbous">10</phase>
<phase date="7/8/2006" text="Waxing Gibbous">11</phase>
<phase date="7/9/2006" text="Waxing Gibbous">12</phase>
<phase date="7/10/2006" text="Waning Gibbous">14</phase>
</moon
The tutorial seems to work well with entries in XML that look like this:
<nighttime>
<txtshort>A moonlit sky</txtshort>
<txtlong>A moonlit sky</txtlong>
<weathericon>33</weathericon>
<hightemperature>100</hightemperature>
<lowtemperature>74</lowtemperature>
<realfeelhigh>108</realfeelhigh>
<realfeellow>74</realfeellow>
</nighttime
But when the XML file has more info (not sure technically what it's called)
like the date and text options above in the phase tag, I'm not sure how to
import these into MS SQL. In MS Access I did do an import from this XML
file, but alas it didn't see these entries in the phase tags either.

Suggestions? I'm trying to use the XML Bulk Load component, but I'm sure
I'm missing something. Thanks for any suggestions ...

Sam

--
Sam Alexander - sam.alexander(at)sidebandbbs(dot)com
http://www.samandflip.com or telnet://sidebandbbs.com
--
"Data is not information, Information is not knowledge, Knowledge is not
understanding, Understanding is not wisdom." -- Cliff Stoll

-- Synchronet 3.13b-Linux NewsLink 1.84
--[SideBand BBS - telnet://sidebandbbs.com]--Re: Importing data into MS SQL 2000 from XML file
By: Sam Alexander to All on Fri Jun 09 2006 09:20 am

Okay I'm getting alittle further... setting my prior question to the side,
now I'm simply trying to get data to import into the database. HEre's what
I've done.

First thing was I installed SQLXML 3.0 sp3 from MS's website and created
my schema and vbs scripts. HEre's my test import:

mydata.xml

<?xml version="1.0"?>
<mydata>
<units>
<temp>F</temp>
<dist>mi</dist>
<speed>mph</speed>
<pres>in</pres>
<prec>in</prec>
</units>
</mydata
schema.xml

<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql"
<ElementType name="temp" dt:type="nvarchar" />
<ElementType name="dist" dt:type="nvarchar" />
<ElementType name="speed" dt:type="nvarchar" />
<ElementType name="pres" dt:type="nvarchar" />
<ElementType name="prec" dt:type="nvarchar" /
<ElementType name="mydata" sql:is-constant="1">
<element type="Units" />
</ElementType
<ElementType name="Units"sql:relation="Units">
<element type="temp"sql:field="temp" />
<element type="dist"sql:field="dist" />
<element type="speed"sql:field="speed" />
<element type="pres"sql:field="pres" />
<element type="prec"sql:field="prec" />
</ElementType
</Schema
import.vbs
Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBL.ConnectionString = "provider=SQLOLEDB.1;data
source=mydbserver;database=mydb;uid=myusername;pwd =mypassword"
objBL.ErrorLogFile = "d:\xml\error.log"
objBL.Execute "d:\xml\schema.xml", "d:\xml\mydata.xml"
Set objBL = Nothing
(I do have my server and login info above ...)

Also I do have a table called Units in the database and here's the layout
of it:
unitstempnvarchar255
unitsdistnvarchar255
unitsspeednvarchar255
unitspresnvarchar255
unitsprecnvarchar255

When I run this I get no message nor any error log. I can test it by
changing the password and I get the unable to connect error, so I know it's
connecting to the server.

ANy suggestions? I checked the MS SQL Server processes anddidn't see any
clue that the script was logging in, but since I'm getting no errors nor
any logs showing what's happening I'm at a loss.

Thanks for any ideas or starting points on where to look. I'm at a total
loss on what to check. Thanks ...

Sam

-- Synchronet 3.13b-Linux NewsLink 1.84
--[SideBand BBS - telnet://sidebandbbs.com]--|||I've noticed you have 'Units' and 'units' - XML is often (always?)
case-sensitive so this may not work.|||To: markc600
Re: Re: Importing data into MS SQL 2000 from XML file
By: markc600 to comp.databases.ms-sqlserver on Fri Jun 09 2006 09:31 am

> From Newsgroup: comp.databases.ms-sqlserver
>
> I've noticed you have 'Units' and 'units' - XML is often (always?)
> case-sensitive so this may not work.
> -- Synchronet 3.13b-Linux NewsLink 1.84

Hi Marc,

Thanks for the reply, and I'll check this out. Since making my post
earlier I've gotten my import to work, but to a point.

My XML file starts with this:
<?xml version="1.0" ?>
<vendor_database xmlns="http://www.vendorurl.com">
-snip-

and here's how my schema.xml file starts ...

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
<xsd:element name="vendor_database" sql:is-constant="true" >
-snip-

If I remove the xmlns entry from the XML data file the import runs fine,
but leaving it in there the import runs but nothing is imported. How do I
work this into the schema file? This vendor file is downloaded
automatically and no way to have this entry deleted from the xml file I"m
importing into my database.

So at this point it's just about there, just need to figure how what to do
with the xmlns entry.

Thanks --

Sam

-- Synchronet 3.13b-Linux NewsLink 1.84
--[SideBand BBS - telnet://sidebandbbs.com]--

Wednesday, March 28, 2012

Importing text from a flat text file.

Monday, March 19, 2012

Importing Excel file to SQL Server (Opinions please)

Friday, March 9, 2012

Importing data into MS SQL 2000 from XML file

Importing Access DB

Blog Archive

About Me