Create table,fields come from csv file

I want to create a table automatically,and fields come from a csv file

any idea? TIA

One way is using two packages and a configuration file.

I'm assuming that you are constructing the name of the table on the fly. Note that the table MUST always have the same format.

First create a sample of what you want your table to look like.

Package 1:
Create an SSIS package that loads data from a CSV file into that table.
Make the tablename come from a variable.
Put the variable in a configuration table

Package 2:
Create a variable that will contain the table name.
Create a variable expressions that has the sql to create the table using the variable previously defined.
Create a variable expression that has the sql to update the variable in the configuration table from Package 1
Create a SQL Task to create the table
Create a SQL Task to update the configuration table variable
Create an Execute SSIS package task to execute Package 1

Similarly you can extend this to the source by scanning directories for files and loading different CSV files into different tables.

Hope this helps,

Larry

create table question

Hi all,
I bring data from 15 different files into several SQL Server tables every
night. I first bring data from CSV file and store them into 15 different
intermediary tables. Then I execute stored procedure that imports data from
intermediary tables into the tables that our users use.
My question is about clearing the intermediary tables. Right now I am
just truncating the tables before importing new data from CSV files. So I
use Insert Into...Select query. But this can create a problem if any of the
columns in the CSV files are removed. So I am thinking if it is worthwhile
droping the tables and then import data using Select...Into query. This way
even if a column is removed, the data will still be imported from the CSV
files. My question is are there any drawbacks in droping and recreating 15
tables everynight instead of truncating them?
Thanks.Actually, if you're database recovery model is not FULL, a SELECT INTO is
going to run significantly faster than an INSERT SELECT, because it's
considered a BULK operation.
Can't think of any drawbacks.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Nikhil Patel" <nikhil0100@.aol.com> wrote in message
news:O9DvE8GNFHA.3512@.TK2MSFTNGP15.phx.gbl...
> Hi all,
> I bring data from 15 different files into several SQL Server tables
> every
> night. I first bring data from CSV file and store them into 15 different
> intermediary tables. Then I execute stored procedure that imports data
> from
> intermediary tables into the tables that our users use.
> My question is about clearing the intermediary tables. Right now I am
> just truncating the tables before importing new data from CSV files. So I
> use Insert Into...Select query. But this can create a problem if any of
> the
> columns in the CSV files are removed. So I am thinking if it is worthwhile
> droping the tables and then import data using Select...Into query. This
> way
> even if a column is removed, the data will still be imported from the CSV
> files. My question is are there any drawbacks in droping and recreating 15
> tables everynight instead of truncating them?
> Thanks.
>|||I can see some drawbacks to creating the tables each time. If columns
are missing or renamed then won't your SPs that access them fail
anyway? You should avoid using SELECT * in production code. Plus,
you'll have to give DDL admin rights to whatever process performs the
load.
One possible approach is to create views that only expose certain
columns in the base table(s) then use a Dynamic Properties task in DTS
to load the data to the appropriate view each time. This does assume
you can read some metadata that determines what columns should be
present. If that metadata isn't available then I would have thought it
desirable to fail the load whenever some data was missing. What if the
file contains zero columns - just empty rows?
David Portas
SQL Server MVP
--sql

Create PerfMon Log and store into SQL DB

Hi.
I want to create a perfmon Logfile and store the Log Data into SQL Database
as offered option instead of CSV or Binary File. I want to use those Data
with Reporting services lateron. But I can't realize it. I have looked for
any articles - without success.
I have proceed as follows:
1. Created a database on test Server to retrieve the log data
2. Created an SQL USER to use this DB
3. Created an ODBC Connection to this SQL Database
4. Created the Log File and defined the ODBC Connection as target for data
After starting I am getting an error message saying sth like: "The Protocol
[name] or warnings have not been started. Refresh the Logfile list to view
the error message. Some protocols or warnings may finish after a few minutes,
especially when using performance indicators."
MarkusMarkus,
The way i did is logged into csv file on all the servers and used DTS to
export data in to the central server.From there we developed custom
procedures to do reports on the data.
Thanks
"MarkusPoehler" wrote:
> Hi.
> I want to create a perfmon Logfile and store the Log Data into SQL Database
> as offered option instead of CSV or Binary File. I want to use those Data
> with Reporting services lateron. But I can't realize it. I have looked for
> any articles - without success.
> I have proceed as follows:
> 1. Created a database on test Server to retrieve the log data
> 2. Created an SQL USER to use this DB
> 3. Created an ODBC Connection to this SQL Database
> 4. Created the Log File and defined the ODBC Connection as target for data
> After starting I am getting an error message saying sth like: "The Protocol
> [name] or warnings have not been started. Refresh the Logfile list to view
> the error message. Some protocols or warnings may finish after a few minutes,
> especially when using performance indicators."
> Markus|||It is never a good idea to log directly to a table from perfmon or trace.
Log to a file and use relog.exe or DST to import it into a table.
--
Andrew J. Kelly SQL MVP
"MarkusPoehler" <poehler@.NOSPAMnetpoint-edv.de> wrote in message
news:8840F9F3-9F5F-4C76-8E6A-929600587FF0@.microsoft.com...
> Hi.
> I want to create a perfmon Logfile and store the Log Data into SQL
> Database
> as offered option instead of CSV or Binary File. I want to use those Data
> with Reporting services lateron. But I can't realize it. I have looked for
> any articles - without success.
> I have proceed as follows:
> 1. Created a database on test Server to retrieve the log data
> 2. Created an SQL USER to use this DB
> 3. Created an ODBC Connection to this SQL Database
> 4. Created the Log File and defined the ODBC Connection as target for data
> After starting I am getting an error message saying sth like: "The
> Protocol
> [name] or warnings have not been started. Refresh the Logfile list to view
> the error message. Some protocols or warnings may finish after a few
> minutes,
> especially when using performance indicators."
> Markus|||Nice Workaround :)
I have found out: You have to define the DSN Connection for MASTER Database
as default without replacing any settings in the DSN Connection Wizard - then
it works. You can find the data inside master in tabesl named
displaytoid, counterdata, counterdetails
--
Markus Pöhler
netpoint-edv gmbh
Germany
"chinn" wrote:
> Markus,
> The way i did is logged into csv file on all the servers and used DTS to
> export data in to the central server.From there we developed custom
> procedures to do reports on the data.
> Thanks
> "MarkusPoehler" wrote:
> > Hi.
> >
> > I want to create a perfmon Logfile and store the Log Data into SQL Database
> > as offered option instead of CSV or Binary File. I want to use those Data
> > with Reporting services lateron. But I can't realize it. I have looked for
> > any articles - without success.
> > I have proceed as follows:
> >
> > 1. Created a database on test Server to retrieve the log data
> > 2. Created an SQL USER to use this DB
> > 3. Created an ODBC Connection to this SQL Database
> > 4. Created the Log File and defined the ODBC Connection as target for data
> >
> > After starting I am getting an error message saying sth like: "The Protocol
> > [name] or warnings have not been started. Refresh the Logfile list to view
> > the error message. Some protocols or warnings may finish after a few minutes,
> > especially when using performance indicators."
> >
> > Markus

Create PerfMon Log and store into SQL DB

Hi.
I want to create a perfmon Logfile and store the Log Data into SQL Database
as offered option instead of CSV or Binary File. I want to use those Data
with Reporting services lateron. But I can't realize it. I have looked for
any articles - without success.
I have proceed as follows:
1. Created a database on test Server to retrieve the log data
2. Created an SQL USER to use this DB
3. Created an ODBC Connection to this SQL Database
4. Created the Log File and defined the ODBC Connection as target for data
After starting I am getting an error message saying sth like: "The Protocol
[name] or warnings have not been started. Refresh the Logfile list to view
the error message. Some protocols or warnings may finish after a few minutes,
especially when using performance indicators."
Markus
Markus,
The way i did is logged into csv file on all the servers and used DTS to
export data in to the central server.From there we developed custom
procedures to do reports on the data.
Thanks
"MarkusPoehler" wrote:

> Hi.
> I want to create a perfmon Logfile and store the Log Data into SQL Database
> as offered option instead of CSV or Binary File. I want to use those Data
> with Reporting services lateron. But I can't realize it. I have looked for
> any articles - without success.
> I have proceed as follows:
> 1. Created a database on test Server to retrieve the log data
> 2. Created an SQL USER to use this DB
> 3. Created an ODBC Connection to this SQL Database
> 4. Created the Log File and defined the ODBC Connection as target for data
> After starting I am getting an error message saying sth like: "The Protocol
> [name] or warnings have not been started. Refresh the Logfile list to view
> the error message. Some protocols or warnings may finish after a few minutes,
> especially when using performance indicators."
> Markus
|||It is never a good idea to log directly to a table from perfmon or trace.
Log to a file and use relog.exe or DST to import it into a table.
Andrew J. Kelly SQL MVP
"MarkusPoehler" <poehler@.NOSPAMnetpoint-edv.de> wrote in message
news:8840F9F3-9F5F-4C76-8E6A-929600587FF0@.microsoft.com...
> Hi.
> I want to create a perfmon Logfile and store the Log Data into SQL
> Database
> as offered option instead of CSV or Binary File. I want to use those Data
> with Reporting services lateron. But I can't realize it. I have looked for
> any articles - without success.
> I have proceed as follows:
> 1. Created a database on test Server to retrieve the log data
> 2. Created an SQL USER to use this DB
> 3. Created an ODBC Connection to this SQL Database
> 4. Created the Log File and defined the ODBC Connection as target for data
> After starting I am getting an error message saying sth like: "The
> Protocol
> [name] or warnings have not been started. Refresh the Logfile list to view
> the error message. Some protocols or warnings may finish after a few
> minutes,
> especially when using performance indicators."
> Markus
|||Nice Workaround
I have found out: You have to define the DSN Connection for MASTER Database
as default without replacing any settings in the DSN Connection Wizard - then
it works. You can find the data inside master in tabesl named
displaytoid, counterdata, counterdetails
Markus P?hler
netpoint-edv gmbh
Germany
"chinn" wrote:
[vbcol=seagreen]
> Markus,
> The way i did is logged into csv file on all the servers and used DTS to
> export data in to the central server.From there we developed custom
> procedures to do reports on the data.
> Thanks
> "MarkusPoehler" wrote:

Create PerfMon Log and store into SQL DB

Hi.
I want to create a perfmon Logfile and store the Log Data into SQL Database
as offered option instead of CSV or Binary File. I want to use those Data
with Reporting services lateron. But I can't realize it. I have looked for
any articles - without success.
I have proceed as follows:
1. Created a database on test Server to retrieve the log data
2. Created an SQL USER to use this DB
3. Created an ODBC Connection to this SQL Database
4. Created the Log File and defined the ODBC Connection as target for data
After starting I am getting an error message saying sth like: "The Protocol
[name] or warnings have not been started. Refresh the Logfile list to vi
ew
the error message. Some protocols or warnings may finish after a few minutes
,
especially when using performance indicators."
MarkusMarkus,
The way i did is logged into csv file on all the servers and used DTS to
export data in to the central server.From there we developed custom
procedures to do reports on the data.
Thanks
"MarkusPoehler" wrote:

> Hi.
> I want to create a perfmon Logfile and store the Log Data into SQL Databas
e
> as offered option instead of CSV or Binary File. I want to use those Data
> with Reporting services lateron. But I can't realize it. I have looked for
> any articles - without success.
> I have proceed as follows:
> 1. Created a database on test Server to retrieve the log data
> 2. Created an SQL USER to use this DB
> 3. Created an ODBC Connection to this SQL Database
> 4. Created the Log File and defined the ODBC Connection as target for data
> After starting I am getting an error message saying sth like: "The Protoco
l
> [name] or warnings have not been started. Refresh the Logfile list to
view
> the error message. Some protocols or warnings may finish after a few minut
es,
> especially when using performance indicators."
> Markus|||It is never a good idea to log directly to a table from perfmon or trace.
Log to a file and use relog.exe or DST to import it into a table.
Andrew J. Kelly SQL MVP
"MarkusPoehler" <poehler@.NOSPAMnetpoint-edv.de> wrote in message
news:8840F9F3-9F5F-4C76-8E6A-929600587FF0@.microsoft.com...
> Hi.
> I want to create a perfmon Logfile and store the Log Data into SQL
> Database
> as offered option instead of CSV or Binary File. I want to use those Data
> with Reporting services lateron. But I can't realize it. I have looked for
> any articles - without success.
> I have proceed as follows:
> 1. Created a database on test Server to retrieve the log data
> 2. Created an SQL USER to use this DB
> 3. Created an ODBC Connection to this SQL Database
> 4. Created the Log File and defined the ODBC Connection as target for data
> After starting I am getting an error message saying sth like: "The
> Protocol
> [name] or warnings have not been started. Refresh the Logfile list to
view
> the error message. Some protocols or warnings may finish after a few
> minutes,
> especially when using performance indicators."
> Markus|||Nice Workaround
I have found out: You have to define the DSN Connection for MASTER Database
as default without replacing any settings in the DSN Connection Wizard - the
n
it works. You can find the data inside master in tabesl named
displaytoid, counterdata, counterdetails
Markus P?hler
netpoint-edv gmbh
Germany
"chinn" wrote:
[vbcol=seagreen]
> Markus,
> The way i did is logged into csv file on all the servers and used DTS to
> export data in to the central server.From there we developed custom
> procedures to do reports on the data.
> Thanks
> "MarkusPoehler" wrote:
>

create mobile database from xls, csv, text or mdb

Hello,

Since my experience in VS is extremely limited, i'd like to be exused if questions sound silly.

My problem is that, at my device application project, i want to build a database that will retrieve data from .xls, or .csv, or .txt, or .mbd files. If i've noticed well, there is no support for OLEDB or ODBC, since by the time that i add tableadapter to my database.xsd and use these, after insertion i get multiple errors informing that system.data.oledb. .... or .odbc "type is not defined".

If it cannot be done, and since i still try to figure out how smart devices function, transact with databases, e.c.t. is there any suggestion?

Thank you in advance

Kostas

you'll likely need to use sql server mobile.|||

I'am trying to do so with sql mobile.

Going-> right click on "deviceapplication1" -> add -> new item -> sql mobile database

This gives me an empty database. In order to fill it i go at server explorer, right click on "AppDatabase2.sdf" (new created database), -> modify connection -> change (data source field) -> i choose microsoft ODBC data source -> user or system data source names: ms access database -> OK. Then browsing for mdb file (my access databace), and it's in server explorer; no problem yet. Then going to solution explorer, opening "AppDatabase2DataSet.xsd", drug & drop tables from server explorer to "AppDatabase2DataSet.xsd" (making table adapters(?)). Then i get this huge error list that "system.data.odbc...." Type is not defined.

Either i need to add some reference (as i did at VBA) since even autofill doesn't work (I try at the top of code to insert "imports system.data.odbc), or it's just not supported in sql mobile database

|||you can't use access as your datasource. appdatabase2.sdf IS your sql server mobile database, and that needs to remain your datasource. no odbc, no ole db in NETCF. your only mobile database option for .net development is a sql server mobile DB.|||

That is something that i have already understood after some experiment.

The question is how sql mobile works and if i can update the tables from an external (server or just a given path) sourse. I have created an sdb, with the tables required, but this tables are empty, and need to be updated each day from source files of the above mentioned format.

|||I'm not a drag and drop type of guy, but from what you've said, you're trying to create a set of adapters between Access and SQL Server Mobile -- you can't do this, there's no ODBC implementation within NETCF for the CF to talk to Access. I learned yesterday from a posting by Ilya Tumanov that there's a util called AccessSynchronizer for Access<-->SQL Server Mobile synchronization, you may want to look into it. Personally I'd just dump the stuff from Access into XML or CSV, and import using my own code, but that may not be for you. Another option is to wrap your datasource in a web service and proxy the sync operation that way.|||

Thank you Andrew, i'll take a look at accessSynchronizer. If my knowledge and experience wasn't so poor i could ask about the code for importing CSV since this is the format that interest me more, but i guess that it will be rather difficult for me fix.

Thank you very much

|||

I believe opennetcf has a csv importer, you may want to consider that ...

this post is a bit old, but may still be relevant:
http://groups.google.com/group/microsoft.public.dotnet.framework.compactframework/browse_thread/thread/671b1480a962e8e0/92d0ba873b07398d%2392d0ba873b07398d

Create Dynamic table from CSV

Hi,

I'm trying to import a csv file directly to my database every month. The contents of the file stays the same, however the format of the columns may vary. For example, 1 month I can have the following:

Time, Probe1, Probe2, Probe3, Probe4

Whereas the next month I can have something like this

Time, Probe2, Probe3, Probe1, Probe4

The "Time" column will always be on the left side, but the probes may vary in their placement.

I'm importing this csv to a temp table, where I then run a query to select any new data and enter that in my main tables. The problem is that when i import the csv, if the placement of the columns has changed, the data gets entered in the wrong locations.

Is there any way to create this temp table dynamically, based on what the header columns of the csv file are?

Any help appreciated!

I think the best approach would be to configure your flat file connection manager to read each row as a single column. This column would then be input to a script transformation component which would parse the line into columns and, based on the column order in the header row, put the values into the appropriate output columns.
|||Here is a code sample for a transformation script to illustrate the idea. The Row.Line is the input column that contains an entire line of a text file. This line is taken and split into an array by the comma delimiter. The first time we see the line, it is a header with column names. A boolean flag is used to detect the header condition and we save the column names in a variable. For every subsequent row, we iterate through the column names and match them to an output column that has been defined for the script, assigning the value parsed from the file into the correct column. Using this method, the order of the columns in the text file is irrelevant.

Code Snippet

Dim Line As String = Row.Line
Dim LineTokens As String() = Line.Split(","c)

If IsHeader Then
HeaderColumns = LineTokens
IsHeader = False
Else
' match input columns to output columns
With Output0Buffer
.AddRow()

For i As Integer = 0 To HeaderColumns.Length - 1
Select Case HeaderColumns(i)
Case "Time"
.Time = LineTokens(i)
Case "Probe1"
.Probe1 = LineTokens(i)
Case "Probe2"
.Probe2 = LineTokens(i)
Case "Probe3"
.Probe3 = LineTokens(i)
Case "Probe4"
.Probe4 = LineTokens(i)
End Select
Next

End With
End If

|||

Thanks a lot for the help...

Looks like it'll solve my problem

Thanks!

|||Just make sure you revist the thread and marks it as answered |||

Ok, Since i'm not very proficient with SSIS...

here's what I have going right now.

Under Data Flow, I have a Flat File Source which uses my Flat File connection manager...there's only 1 column, just like you said.

Then I have it going to the transformation script component.

Under Input Columns, I only have Column 0, and its output alias is the same.

Under Inputs & Outputs, I didn't change anything...I didn't add any output columns since I want this to be done dynamically...

Then for the script,

I put in the script you gave me, and this is where i'm having problems...

I renamed Row.Line to Row.Column0,

but there's still a few undefined...such as IsHeader, HeaderColumns, and OutputBuffer.

Where do I define these?!

Thanks!

|||IsHeader and HeaderColumns are global variables that I didn't show in the example. They are shown below would go right under "Inherits UserComponents" in the script.

Code Snippet

Dim IsHeader As Boolean = True
Dim HeaderColumns() As String

Output0Buffer (don't forget the "0") is built for you to the definition that you supply on "Inputs and Outputs". This object will have the same name as your output (without spaces), but with "Buffer" appended to it. You should have one now, but likely just forgot to add the "0" (the default output is "Output 0" ).

You can't leave your output columns blank as they can't be created dynamically. You said your columns were constant, but could appear in different orders in the source. The script will worry about the order of the input columns, but you still need to define the constant output columns.

|||

Beauty.

It's working now.

Thanks a lot for the help.

Create CSV from SQL query

Hello,
I would like to be able to create a csvfile when I execute a query in
SQL. Is there an SQL command or procedure that allows for this?
If a query is executed, how could I dump the results into a CSV file (or
any other format - but preferibly csv). I would rather not used
externalized tools (ie write my own echo or cscript, or DLL to write to a
file within a SQL function or procedure).
Thank you
James.James,
You can do it easily in a DTS package. Just create a
source server and an output text file(you can set the
delimter in the file properties. Create a transform data
task and place your query in the task. Then run the DTS
package to output the results to the file.
I hope that helps.
Derek
>--Original Message--
>Hello,
> I would like to be able to create a csvfile when I
execute a query in
>SQL. Is there an SQL command or procedure that allows
for this?
> If a query is executed, how could I dump the results
into a CSV file (or
>any other format - but preferibly csv). I would rather
not used
>externalized tools (ie write my own echo or cscript, or
DLL to write to a
>file within a SQL function or procedure).
> Thank you
> James.
>
>.
>