Raster Catalog: example

Showing posts with label example. Show all posts

Friday, March 30, 2012

reading a txt file

I import data by omporting a txt file that is tab delimited.
On of the fileds in the txt file has a date 08/06/2007 (for example)
I need to read that into my field in my table that is set as datetime.
Is there somethign special that need to get done? When I try, i get teh
following error:
Conversion invalid for datatypes on column pair 1 (source column
'DoRecd'(dbstype_str), destination column 'doRecd'(DBTYPE_DBtimestamp)On Aug 6, 11:14 pm, "Johnfli" <j...@.ivhs.us> wrote:
> I import data by omporting a txt file that is tab delimited.
> On of the fileds in the txt file has a date 08/06/2007 (for example)
> I need to read that into my field in my table that is set as datetime.
> Is there somethign special that need to get done? When I try, i get teh
> following error:
> Conversion invalid for datatypes on column pair 1 (source column
> 'DoRecd'(dbstype_str), destination column 'doRecd'(DBTYPE_DBtimestamp)
You should change the source data type to datetime or use text for
both.|||it's a text file.
Before I export it to a text file, I have it formated as datetime in excel.
"SB" <othellomy@.yahoo.com> wrote in message
news:1186485944.181307.110770@.w3g2000hsg.googlegroups.com...
> On Aug 6, 11:14 pm, "Johnfli" <j...@.ivhs.us> wrote:
>> I import data by omporting a txt file that is tab delimited.
>> On of the fileds in the txt file has a date 08/06/2007 (for example)
>> I need to read that into my field in my table that is set as datetime.
>> Is there somethign special that need to get done? When I try, i get teh
>> following error:
>> Conversion invalid for datatypes on column pair 1 (source column
>> 'DoRecd'(dbstype_str), destination column 'doRecd'(DBTYPE_DBtimestamp)
> You should change the source data type to datetime or use text for
> both.
>|||On Aug 8, 3:51 am, "Johnfli" <j...@.ivhs.us> wrote:
> it's a text file.
> Before I export it to a text file, I have it formated as datetime in excel.
> "SB" <othell...@.yahoo.com> wrote in message
> news:1186485944.181307.110770@.w3g2000hsg.googlegroups.com...
>
> > On Aug 6, 11:14 pm, "Johnfli" <j...@.ivhs.us> wrote:
> >> I import data by omporting a txt file that is tab delimited.
> >> On of the fileds in the txt file has a date 08/06/2007 (for example)
> >> I need to read that into my field in my table that is set as datetime.
> >> Is there somethign special that need to get done? When I try, i get teh
> >> following error:
> >> Conversion invalid for datatypes on column pair 1 (source column
> >> 'DoRecd'(dbstype_str), destination column 'doRecd'(DBTYPE_DBtimestamp)
> > You should change the source data type to datetime or use text for
> > both.- Hide quoted text -
> - Show quoted text -
Hi,
I think it be easier if you import the text file AS IS that is all
columns as text (including the column with datetime) you can call it
Excel_to_text_TEMP. Once you have this table imported in the server
you can populate your main table from this temp table and use a
conversion function for datetime etc. HTH.|||I do intailly load it into a tmp table, as after its in teh temps table, I
have it go and only copy new original records into the main table, I also
have it update exsisting records. so with that said...
How do I convert it to datetime as it goes from the tmp table to teh main
table?
I am not an sql master or anythign, heck, i'm not ever a sql novice lol
so please have your example as clear as possible..
THANK YOU
"SB" <othellomy@.yahoo.com> wrote in message
news:1186545477.061884.9860@.19g2000hsx.googlegroups.com...
> On Aug 8, 3:51 am, "Johnfli" <j...@.ivhs.us> wrote:
>> it's a text file.
>> Before I export it to a text file, I have it formated as datetime in
>> excel.
>> "SB" <othell...@.yahoo.com> wrote in message
>> news:1186485944.181307.110770@.w3g2000hsg.googlegroups.com...
>>
>> > On Aug 6, 11:14 pm, "Johnfli" <j...@.ivhs.us> wrote:
>> >> I import data by omporting a txt file that is tab delimited.
>> >> On of the fileds in the txt file has a date 08/06/2007 (for
>> >> example)
>> >> I need to read that into my field in my table that is set as datetime.
>> >> Is there somethign special that need to get done? When I try, i get
>> >> teh
>> >> following error:
>> >> Conversion invalid for datatypes on column pair 1 (source column
>> >> 'DoRecd'(dbstype_str), destination column 'doRecd'(DBTYPE_DBtimestamp)
>> > You should change the source data type to datetime or use text for
>> > both.- Hide quoted text -
>> - Show quoted text -
> Hi,
> I think it be easier if you import the text file AS IS that is all
> columns as text (including the column with datetime) you can call it
> Excel_to_text_TEMP. Once you have this table imported in the server
> you can populate your main table from this temp table and use a
> conversion function for datetime etc. HTH.
>|||On Aug 8, 10:06 pm, "Johnfli" <j...@.ivhs.us> wrote:
> I do intailly load it into a tmp table, as after its in teh temps table, I
> have it go and only copy new original records into the main table, I also
> have it update exsisting records. so with that said...
> How do I convert it to datetime as it goes from the tmp table to teh main
> table?
> I am not an sql master or anythign, heck, i'm not ever a sql novice lol
> so please have your example as clear as possible..
> THANK YOU
> "SB" <othell...@.yahoo.com> wrote in message
> news:1186545477.061884.9860@.19g2000hsx.googlegroups.com...
>
> > On Aug 8, 3:51 am, "Johnfli" <j...@.ivhs.us> wrote:
> >> it's a text file.
> >> Before I export it to a text file, I have it formated as datetime in
> >> excel.
> >> "SB" <othell...@.yahoo.com> wrote in message
> >>news:1186485944.181307.110770@.w3g2000hsg.googlegroups.com...
> >> > On Aug 6, 11:14 pm, "Johnfli" <j...@.ivhs.us> wrote:
> >> >> I import data by omporting a txt file that is tab delimited.
> >> >> On of the fileds in the txt file has a date 08/06/2007 (for
> >> >> example)
> >> >> I need to read that into my field in my table that is set as datetime.
> >> >> Is there somethign special that need to get done? When I try, i get
> >> >> teh
> >> >> following error:
> >> >> Conversion invalid for datatypes on column pair 1 (source column
> >> >> 'DoRecd'(dbstype_str), destination column 'doRecd'(DBTYPE_DBtimestamp)
> >> > You should change the source data type to datetime or use text for
> >> > both.- Hide quoted text -
> >> - Show quoted text -
> > Hi,
> > I think it be easier if you import the text file AS IS that is all
> > columns as text (including the column with datetime) you can call it
> > Excel_to_text_TEMP. Once you have this table imported in the server
> > you can populate your main table from this temp table and use a
> > conversion function for datetime etc. HTH.- Hide quoted text -
> - Show quoted text -
Hi,
Then you should drop the TEMP table and re-create it where all columns
should be varchar(255) or varchar(1000) etc. Then once the temp table
is loaded you should run a query such as (preferably in a sored
procedure, see Books online for syntax):
insert Excel_To_Text (
column1,
column2,
etc...,
date_Column1,
etc...)
select column1,
column2,
etc...,
convert(datetime,Date_Column1),
etc...
from Excel_To_Text_TEMP
Let me know if you have questions. Regards,
SB|||SB <othellomy@.yahoo.com> wrote in
news:1186631817.654696.162750@.19g2000hsx.googlegroups.com:
> On Aug 8, 10:06 pm, "Johnfli" <j...@.ivhs.us> wrote:
>> I do intailly load it into a tmp table, as after its in teh temps
>> table, I have it go and only copy new original records into the main
>> table, I also have it update exsisting records. so with that
>> said...
>> How do I convert it to datetime as it goes from the tmp table to teh
>> main table?
>> I am not an sql master or anythign, heck, i'm not ever a sql novice
>> lol
>> so please have your example as clear as possible..
>> THANK YOU
>> "SB" <othell...@.yahoo.com> wrote in message
>> news:1186545477.061884.9860@.19g2000hsx.googlegroups.com...
>>
>> > On Aug 8, 3:51 am, "Johnfli" <j...@.ivhs.us> wrote:
>> >> it's a text file.
>> >> Before I export it to a text file, I have it formated as datetime
>> >> in excel.
>> >> "SB" <othell...@.yahoo.com> wrote in message
>> >>news:1186485944.181307.110770@.w3g2000hsg.googlegroups.com...
>> >> > On Aug 6, 11:14 pm, "Johnfli" <j...@.ivhs.us> wrote:
>> >> >> I import data by omporting a txt file that is tab delimited.
>> >> >> On of the fileds in the txt file has a date 08/06/2007 (for
>> >> >> example)
>> >> >> I need to read that into my field in my table that is set as
>> >> >> datetime.
>> >> >> Is there somethign special that need to get done? When I try,
>> >> >> i get teh
>> >> >> following error:
>> >> >> Conversion invalid for datatypes on column pair 1 (source
>> >> >> column 'DoRecd'(dbstype_str), destination column
>> >> >> 'doRecd'(DBTYPE_DBtimestamp)
>> >> > You should change the source data type to datetime or use text
>> >> > for both.- Hide quoted text -
>> >> - Show quoted text -
>> > Hi,
>> > I think it be easier if you import the text file AS IS that is all
>> > columns as text (including the column with datetime) you can call
>> > it Excel_to_text_TEMP. Once you have this table imported in the
>> > server you can populate your main table from this temp table and
>> > use a conversion function for datetime etc. HTH.- Hide quoted text
>> > -
>> - Show quoted text -
> Hi,
> Then you should drop the TEMP table and re-create it where all columns
> should be varchar(255) or varchar(1000) etc. Then once the temp table
> is loaded you should run a query such as (preferably in a sored
> procedure, see Books online for syntax):
> insert Excel_To_Text (
> column1,
> column2,
> etc...,
> date_Column1,
> etc...)
> select column1,
> column2,
> etc...,
> convert(datetime,Date_Column1),
> etc...
> from Excel_To_Text_TEMP
> Let me know if you have questions. Regards,
> SB
You might eliminate the text file and instead:
SELECT F1 AS [Col1], ... CAST(Fn AS datetime) AS [DateCol], ...
INTO [DestinationTable]
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;HDR=YES;Database=full_path_to\filename.xls',
'SELECT * FROM [SheetName$]')
Use the Microsoft.ACE.OLEDB.12.0 provider if you don't have Jet or if the
Excel version is 2007 (and, for Excel 2007, change Excel 8.0 to Excel
12.0).
You will probably find this a little tricky to get working (I surely
did!) so build up from a simple import, e.g. of a single integer column.sql

Reading a Filename which has a Wildcard

Hi - I need to read a file but I do not know the whole filename!

For example, I need to read a file whose name is:- Finance_200510.xls

I know the prefix is "Finance" and I know the suffix is ".xls", but the bit in the middle could be anything. In the above example, it is a date which will vary from month to month.

I could achieve this as follows:-

1. I already need a .CMD file which contains a DTEXEC command.
2. So, I could insert the following line before the DTEXEC command:-
DIR Finance*.xls > ListFile.txt
(Alternatively, this command could be invoked from within the Package)
3. I could read the file: ListFile.txt using a SSIS Script Component
and extract the first occurrence of the string starting with "Finance"
and ending with ".xls" and save the filename to a package variable: @.[user::filename]
4. The package variable @.[user::filename] could be used in the expression property of a Connection Manager
5. The Connection Manager could then read the file.
6. Optionally, I could generate a RENAME command using a Script Component to rename Finance_200510.xls to Finance_200510.done so that it is not re-processed.

Can anybody suggest a simpler solution, please?!

Thanks.You can use multiFlatfile connection to provide file name with wildcard. In your case, you will have to make sure that there are no other unwanted files in that folder which might match the wildcard you are using. This will eliminate the need for listing all the files in the folder, and use script component to get the file whose name starts with "Finance". In your multiflatfile connection, you can just use Finance*.xls, and it will catch your needed file.

See BOL topic
ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.SQL.v2005.en/extran9/html/31fc3f7a-d323-44f5-a907-1fa3de66631a.htm
for more on Multiple Flat File Connection Manager. The ideal use for Multiple flat file connection manager is to process more than one file which are of the same format (instead of using For Each Loop), but it will also solve your purpose of choosing file with Name that matches a wildcard.

HTH,
Ranjeeta Nanda|||Or similarly, you could use the Foreach Loop with the For Each File enumerator and a file pattern of "Finance*.xls."

If you really really wanted the exact name of the file before you started processing, or needed to check for other conditions, you could use the System.IO namespace within a Script Task to retrieve files that match the pattern, make sure there's only one, and pop the filename into a package variable for use by the next task.

-Doug

Wednesday, March 28, 2012

Read\Write Files in SQL 2000

Hi, I was just wondering if it was possible/ the best way to read and write text files for example in SQL 2000?

Is it only possible to do this using extended stored procedures or do SQL commands exist to perform file IO?

Thanks very much,

Ewen

What is your purpose for this ? Do you intend to write query results to text file ? You can use BCP to do that. Or for importing a text file into a table ? use BULK INSERT

Read Value from XML

This is My example :
DECLARE @.idoc int
DECLARE @.doc varchar(4000)
SET @.doc ='
<NEWDATASET>
<TABLE1>
<INTERO>1</INTERO>
</TABLE1>
</NEWDATASET> '
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
Select * FROM OPENXML (@.idoc, '/NEWDATASET/TABLE1',1)
WITH (INTERO int)
Not function, why?
John,
You need to identify the element in the WITH clause... also, don't forget to
remove the document reference when you're done:
DECLARE @.idoc int
DECLARE @.doc varchar(4000)
SET @.doc ='
<NEWDATASET>
<TABLE1>
<INTERO>1</INTERO>
</TABLE1>
</NEWDATASET> '
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
Select * FROM OPENXML (@.idoc, '/NEWDATASET/TABLE1',1)
WITH (INTERO int 'INTERO')
exec sp_xml_removedocument @.idoc
"John" <ch@.msn.com> wrote in message
news:%23id3kIfsEHA.2128@.TK2MSFTNGP11.phx.gbl...
> This is My example :
> DECLARE @.idoc int
> DECLARE @.doc varchar(4000)
> SET @.doc ='
> <NEWDATASET>
> <TABLE1>
> <INTERO>1</INTERO>
> </TABLE1>
> </NEWDATASET> '
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> Select * FROM OPENXML (@.idoc, '/NEWDATASET/TABLE1',1)
> WITH (INTERO int)
>
> Not function, why?
>

Wednesday, March 21, 2012

read cvs in c# and insert into sql server

Hi,

Anybody has an example of reading a csv comma delimited file and insert the fields into a datatable?

Thanks

You have serveral options: bcp utility, DTS (SSIS in SQL 2005), Import/Export Wizard, or bulk insert command. DTS is much easier than others, you can start from here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_tools_des_07xh.asp

Or you can use the Import/Export Wizard:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_tools_wiz_8vsj.asp

|||

Sorry, I must've posted to the wrong forum. I thought I posted into the LogParser one. I'll repost there. Yeah, I was asking if anybody knows how to use LogParser to read a custom cvs log file into the db. Thanks though.

Saturday, February 25, 2012

RDA oleDBConnectionString problem

hi there,
can anyone please show me an example of RDAoleDBConnectionString? i was playing around with RDA, but i cant pull data with my connection string, with an error message
failure to open SQL Server with given connct string. [ connect string =
Data Source = JUN; Initial Catalog = SmartShopper; Integrated Security =True ]
i need a sample of connection string to connect to SQL server 2005, i was using VB 2005 to deploy my project

You need to specify a provider in the OleDb connection string. See http://www.connectionstrings.com/ for examples.

But if you want to connect to a SQL Server, you should probably use the Sql-specific types instead of OleDb.

|||thanks for your reference site...i get quite a lot info there..

Monday, February 20, 2012

Rather Simple Logic - Need some advice

I have two tables and I want to validate that the results of a query
match the required structure. In this example, the Bad Query returns
two Row2, it therefore does not match the required structure.
Thanks for the help.
Dave
Actual Results from a Query (Good)
---
PartNo Row Description
6F23-1700034-AP32NC 1 V65-F001G
6F23-1700034-AP32NC 2 V65-S002G
6F23-1700034-AP32NC 3 V65-T032G
Actual Results from a Query (Bad)
---
PartNo Row Description
6F23-1700034-AP32NC 1 V65-F001G
6F23-1700034-AP32NC 2 V65-S002G
6F23-1700034-AP32NC 3 V65-T003G
6F23-1700034-AP32NC 2 V65-S002G
Required Structure
--
Row Description
1 1st Row
2 2nd Row
3 3rd RowA precise definition of 'match required structure' is needed to answer your
question.
IF (SELECT COUNT(*) FROM RequiredStructure) =
(SELECT COUNT(*) FROM ActualResults) AND
(SELECT COUNT(*) FROM RequiredStructure) =
(SELECT COUNT(*)
FROM(
SELECT Row FROM RequiredStructure
UNION
SELECT Row FROM ActualResults
) AS CompareResults
)
PRINT 'Matches'
ELSE
PRINT 'Does not match'
Hope this helps.
Dan Guzman
SQL Server MVP
"Dave" <davel@.here.ca> wrote in message
news:r0em72ln86fhtc5b9gammvvn72d2f7h68f@.
4ax.com...
>I have two tables and I want to validate that the results of a query
> match the required structure. In this example, the Bad Query returns
> two Row2, it therefore does not match the required structure.
> Thanks for the help.
> Dave
>
> Actual Results from a Query (Good)
> ---
> PartNo Row Description
> 6F23-1700034-AP32NC 1 V65-F001G
> 6F23-1700034-AP32NC 2 V65-S002G
> 6F23-1700034-AP32NC 3 V65-T032G
> Actual Results from a Query (Bad)
> ---
> PartNo Row Description
> 6F23-1700034-AP32NC 1 V65-F001G
> 6F23-1700034-AP32NC 2 V65-S002G
> 6F23-1700034-AP32NC 3 V65-T003G
> 6F23-1700034-AP32NC 2 V65-S002G
> Required Structure
> --
> Row Description
> 1 1st Row
> 2 2nd Row
> 3 3rd Row
>

Friday, March 30, 2012

reading a txt file

reading a txt file

reading a txt file

Reading a Filename which has a Wildcard

Wednesday, March 28, 2012

Read\Write Files in SQL 2000

Read Value from XML

Wednesday, March 21, 2012

read cvs in c# and insert into sql server

Saturday, February 25, 2012

RDA oleDBConnectionString problem

Monday, February 20, 2012

Rather Simple Logic - Need some advice

Raster Catalog

Blog Archive

About Me