code for local utility: csv to tsql file with create table & insert sql commands
code for local utility: csv to tsql file with create table & insert sql commands
code for local utility: csv to tsql file with create table & insert sql commands
code for local utility: csv to tsql file with create table & insert sql commands
code for local utility: csv to tsql file with create table & insert sql commands
code for local utility: csv to tsql file with create table & insert sql commands code for local utility: csv to tsql file with create table & insert sql commands code for local utility: csv to tsql file with create table & insert sql commands code for local utility: csv to tsql file with create table & insert sql commands code for local utility: csv to tsql file with create table & insert sql commands code for local utility: csv to tsql file with create table & insert sql commands code for local utility: csv to tsql file with create table & insert sql commands code for local utility: csv to tsql file with create table & insert sql commands
code for local utility: csv to tsql file with create table & insert sql commands code for local utility: csv to tsql file with create table & insert sql commands
code for local utility: csv to tsql file with create table & insert sql commands
Go Back  Xtreme Visual Basic Talk > > > code for local utility: csv to tsql file with create table & insert sql commands


Reply
 
Thread Tools Display Modes
  #1  
Old 12-23-2012, 09:02 PM
bokeh bokeh is offline
Regular
 
Join Date: Aug 2012
Posts: 60
Default code for local utility: csv to tsql file with create table & insert sql commands


If you do a search of the forums for "GoDaddy" you'll find numerous threads that talk about
the difficulty of making remote connections to a GoDaddy's microsoft server databases.

Basically remote database connections are not allowed under the way they have IIs 6.0 setup,
and my employer (that I am interning for) will not allow me to choose the "Upgrade to IIs 7.0 server" option
which does allow remote database connections.

So this basically rules out locally using SQL Express, MsServer, or anything else
that use/requires a remote connection to a online database.

If you go into the web admin tool that GoDaddy uses for their Microsoft SQL Server databses there are only two options:
Option 1.) CSV import -which doesn't not only does not create a table from field columns
(which is typical of most MySQL phpmyadmin's csv imports I have seen),
but also doesn't work at all unless the table has been created manually beforehand
(which can only be done by copy/pasting raw T-SQL ascii type SQL commands into
the GoDaddy web admin Query Analyzer "tool" --which leads to option2..)

Option 2.) The GoDaddy Web Admin Query Analyzer tool.
It's not really an "Analyzer" so much as a web form textbox to remotely issue commands
directly to the GoDaddy virtualized version of the SQL Server (via port 80 I think).

A typical "create table" command (in the way that the GoDaddy Query Analyzer understands) would look something like:
Quote:
USE [databasename]
GO
CREATE TABLE [dbo].[tablename](
[field0] [int] NOT NULL IDENTITY(1,1),
[field1] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[field2] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[field3] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[field4] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[field5] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[field6] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[field7] [int] NULL,
CONSTRAINT [PK_Inventory] PRIMARY KEY CLUSTERED
(
[field0] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
...and a typical set of Insert commands
(used to insert data into the pre-created table in a way that the GoDaddy QA could understands), would look like:
Quote:
USE [databasename]
GO
INSERT [dbo].[tablename] ([field1], [field2], [field3], [field4], [field5], [field6], [field7]) VALUES ('999', '1015', 'item1', '4', '7.99', '0', 004294967295)
INSERT [dbo].[tablename] ([field1], [field2], [field3], [field4], [field5], [field6], [field7]) VALUES ('999', '1050', 'item2', '1', '6.99', '1', 004294967295)
Unfortunately what I have now is not an .sql file (with t-sql commands inside),
but a csv (comman separate values) file that needs to be converted.

This .csv file is basically just a comman delimited ascii file which has been exported from
excel using "Save As" (and choosing the .csv file option).

The very first line of this csv file has/contains the table column names in it (field0, field1, etc)
All the other subsequent lines (several thousand of them) are data.

So what I need is way to import a csv file and export (create) an .sql file with
both the necessary create table and insert t-SQL (transact sql) commands.

I'm look for a basic (simple) solution that doesn't require BCD, Bulk Inserts, sql infile commands,
or using any kind of data bound controls (like a datagridview).

The user interface for the utility has only two textboxes controls:
1.) A text box to specify the input .csv file path
2.) A text box to specify the utput .sql file path
Maybe some code to call the file selector dialog
and a label or a messagebox that says "Done!"

Even though I am a VB.Net newbie I know:

How to use the File Open dialog:
Let users pick a file to open via OpenFileDialog in VB.NET
..and how to use to use the OpenFileDialog Class in combination with a file filter:
java2s site VB.Net code : File Open dialog: file filter

How to read the lines of a file text and put them into an array:
MSDN: Read Text from a File
Read All Lines from a Text File into a String Array
FileToArray - Reading all lines from a text file into a String array
Reading Text File then Spliting it up in VB .NET

But after I get the contents of the csv file into a VB.Net array them what?

How do I write code to have well-formed create table and insert commands?
This I haven't found an good examples of...especially with any kind of
rudimentary validation in cases where the csv might not be totally properly formatted.

Maybe in the case where any validation code notices something strange
(like two commas right next to each other with nothing intervening)
it should dump the lines into a multiline textbox,
and show (in another multiline textbox below the csv text output one)
the possibly messed up sql text lines (bolding the text where the "some strange" part is happening)?

At least this way maybe the user/devloper using the utility can figure out what needs to be done
(just in case there is no way to write csv cleanup and auto-correction code --I haven't found any good VB.Net examples of this either).

Anyway creating text in VB.Net isn't that hard it's just the
csv text validation and wirting SQL commands part that is throwing me.

I have read through a bunch of online tutorials for transact SQL
(and looked at the related MSDN documentation)
but my brain is just turning to mush when it comes to this TSQL stuff.
Note: It has taken me many many weeks of research (as well as trial & error) to
figure out the formatting for the working GoDaddy QA SQL snippets above.

Please, any help appreciated..thanks!

Last edited by bokeh; 12-23-2012 at 09:43 PM.
Reply With Quote
  #2  
Old 12-24-2012, 06:36 PM
mmcginty mmcginty is offline
Newcomer
 
Join Date: Apr 2009
Location: Central California Coast
Posts: 13
Default

Your approach to this problem is neither robust, fault tolerant nor particularly efficient, I would go an entirely different direction: I would create a simple WebService on the provider and push the data up as DataTables. That way you'd be able to read the structure of the data from the DataTable, recreate it on the server if necessary, and then use native .Net mechanisms to insert the data into tables. It would be a powerful, well-supported and highly extensible mechanism.

You could use ODBC to open any Excel file as a DataTable directly, no save as CSV step, and parsing/validation would be all but handled for you. (You'd have to handle a few exceptions, but that's about it.)

I have some code on file I've been meaning to port, that generates T-SQL DDL statements from a Recordset, and I have tons of WebService code that accepts and utilizes posted DataTables and DataSets.

If you're interested in considering this sort of change in direction, I'll post some code to get you started.
Reply With Quote
  #3  
Old 12-25-2012, 04:36 PM
bokeh bokeh is offline
Regular
 
Join Date: Aug 2012
Posts: 60
Default Code?

Quote:
Originally Posted by mmcginty
I have some code on file I've been meaning to port, that generates T-SQL DDL statements from a Recordset,
and I have tons of WebService code that accepts and utilizes posted DataTables and DataSets.

If you're interested in considering this sort of change in direction, I'll post some code to get you started.
If you have some a working demo fo some Web Service ODBC code please feel free to post it as an attachment.

The diificulty may be in the "create a simple WebService on the provider" part.
GoDaddy has truly vicious security restrictions and they give no way to change the TCP Ports allowed
under their security restricted IIs 6.0 configuration.

On my particular "virtualized server" setup they block TCP port 81, 443,
most of the 5000 and 8000 series (including 8080) as well as 16080.

If the Web Services code uses port 80, there's a possibility it might work,
but I've had trouble connecting with just about every SOAP code I could find.

However I know that some people are able to run ASP.Net Web Services on GoDaddy's virtualized server setup,
so that may present a workaround..but you have to know how to work with their particular ASP.Net default schema,
which is not well documented on their part.

Last edited by bokeh; 12-25-2012 at 04:46 PM.
Reply With Quote
  #4  
Old 12-25-2012, 08:00 PM
mmcginty mmcginty is offline
Newcomer
 
Join Date: Apr 2009
Location: Central California Coast
Posts: 13
Default

Quote:
Originally Posted by bokeh View Post
If you have some a working demo fo some Web Service ODBC code please feel free to post it as an attachment.

The diificulty may be in the "create a simple WebService on the provider" part.
GoDaddy has truly vicious security restrictions and they give no way to change the TCP Ports allowed
under their security restricted IIs 6.0 configuration.

On my particular "virtualized server" setup they block TCP port 81, 443,
most of the 5000 and 8000 series (including 8080) as well as 16080.

If the Web Services code uses port 80, there's a possibility it might work,
but I've had trouble connecting with just about every SOAP code I could find.

However I know that some people are able to run ASP.Net Web Services on GoDaddy's virtualized server setup,
so that may present a workaround..but you have to know how to work with their particular ASP.Net default schema,
which is not well documented on their part.
If you can run anything .Net on your host you should be able to run a WebService, port 80 is all it needs. I run a few of them on my provider's shared hosting so I think is should be comparable.

As for the default schema variants I gave up on them some time ago, I just exempt the WebService source file from authentication in web.config, and pass credentials in with every method -- I know, it's less than elegant... necessity has given birth to so many inventions, she can't always look her best. :-) Anyways, form authentication and WebServices don't really mix well.

As for the ODBC part, that's strictly client side, I was going to leave that to you. :-) The end goal is a System.Data.DataTable object, opened on whatever your data source was -- Excel, correct? I'm absolutely certain it's possible. From there the WebService is data-source-agnostic. It doesn't matter where the data came from, it doesn't even matter if the column names match, you can map them with the DataAdapter.

You pass a DataTable or DataSet as a parameter and the object is remoted just like that.

I've got family in town for the holidays, so give me a couple of days to post some code, ok?
Reply With Quote
  #5  
Old 12-26-2012, 04:29 PM
bokeh bokeh is offline
Regular
 
Join Date: Aug 2012
Posts: 60
Default thanks for the reply..

Quote:
Originally Posted by mmcginty
As for the default schema variants I gave up on them some time ago... Anyways, form authentication and WebServices don't really mix well.
That's actually good news - the whole schema thing looked like a difficult learning curve to climb.
Quote:
Originally Posted by mmcginty
As for the ODBC part, that's strictly client side, I was going to leave that to you. :-) The end goal is a System.Data.DataTable object,
opened on whatever your data source was -- Excel, correct? I'm absolutely certain it's possible.
It may be possible but certainly not obvious or easy.
I found many more examples of connecting a DataSet/DataTable to a csv file than I did to using Excel files,
but maybe I wasn't looking in the right places.

I tried to read through a few SQL Express ODBC tutorials dealing with Excel files but it just got in too deep for me.
Quote:
Originally Posted by mmcginty
From there the WebService is data-source-agnostic. It doesn't matter where the data came from,
it doesn't even matter if the column names match, you can map them with the DataAdapter.
You pass a DataTable or DataSet as a parameter and the object is remoted just like that.
I've been reading up on things like n-tier (1, 2, 3) and DTOs (Data Transfer Objects).

This MSDN DTO page has a nice diagram that I thing tries to explain/show the use of DTOs with a web service.
I realize DTOs have both pros and cons, but the main "con" seem to be
the time it takes to write the code to implement DTOs correctly.

I've also been reading up about DTOs versus POCOs.
The guy who wrote that article had a nice 3 part series on
High Performance Data Access Layers (1, 2, 3), but its all C# code way over my head.

The thing is that I will probably be using a DataGridView control for display
of the data (if not editing), and my three options seem to be:
1.) Bound
2.) Unbound
3.) Virtual
This page
says:
Quote:
Bound mode is suitable for managing data using automatic interaction with the data store.
One very common use of the DataGridView control is binding to a table in a database.
Unbound mode is suitable for displaying relatively small amounts of data that you manage programmatically.
Virtual mode gives you a higher degree of control by allowing you to wait
until a cell is actually being displayed to provide the value it will contain.
What the page doesn't say is that there is a big performance decrease in choosing unbound versus bound
and pretty much all the virtual examples were way too elaborate to understand.

The issue with a directly bound DatagridView control, though, is that it takes several seconds on my employer's slow internet connection
to fetch the data from GoDaddy, during which time the user interface is essentially "locked up".

Is it possible to write a DAL (Data Abstarction Layer) in such a way that
the data can be stored and kept updated in a another thread (like a BackgroundWorker thread),
using some type of in-memory data storage structure (DataSet/DataTable, etc),
then transfer the data into the GUI thread when needed, either polling the thread with the data or
somehow interrupting it to say "feed me the latest updated data now"?

I've been researching this but haven'ty found any good examples.
I did find doing search-as-you-type filtering for the DataGridView control is called "autocomplete",
and there is some VB.Net sample code here.

The thing is what about wildcards in the middle of words?
SQL uses the underscore charcter "_" for single charcter wildcard matching in the middle of a string,
but this MSDN page seems to suggest there is no simple way to do that when filtering for a DataGridView control.
Quote:
Originally Posted by mmcginty
I've got family in town for the holidays, so give me a couple of days to post some code, ok?
Please do not neglect your family for me or the forum in general
(I know the forum can be very enticing/addicting but it's all a matter of prioritizing).

I'm still working on the csv to tsql create tabel and insert sql commands utility and probably will continue well past New Years.

Last edited by bokeh; 12-26-2012 at 05:07 PM.
Reply With Quote
  #6  
Old 12-27-2012, 04:41 AM
mmcginty mmcginty is offline
Newcomer
 
Join Date: Apr 2009
Location: Central California Coast
Posts: 13
Default some code...

Here's a quick function that creates a DataTable from an .xlsx file (given that it has a sheet named sheet1 with contents formatted like tabular data):

Code:
Public Function DataTableFromXLSX() As DataTable
    Dim cn As New OdbcConnection("Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=c:\temp\myfile.xlsx;")
    cn.Open()
    Dim dt As New System.Data.DataTable()
    Dim cmd As New System.Data.Odbc.OdbcCommand("select * from [sheet1$]", cn)
    Dim dr As System.Data.Odbc.OdbcDataReader = cmd.ExecuteReader()
    dt.Load(dr)
    Return dt
End Function
To make it work with .csv, the only differences would be the connection string and the SQL statement.

From there you'll have a columns collection, and each column object has properties from which DDL can be derived. I looked into porting the classic ADODB code I mentioned, but hadn't realized how much more cut-and-dried the data type info was. So this is a way over-simplified and very much incomplete (otoh, possibly adequate for xlsx or csv data) example of what code to derive DDL from a DataTable object might look like:

Code:
Public Function TableDDL(dt As DataTable) As String
    Dim buf As String = ""
    Dim i As Integer = 0
    For i = 0 To dt.Columns.Count - 2
        buf += vbCrLf + ColumnDDL(dt.Columns.Item(i), ",")
    Next
    buf += vbCrLf + ColumnDDL(dt.Columns.Item(dt.Columns.Count - 1))
    Return "CREATE TABLE [" + dt.TableName + "] (" + buf + vbCrLf + ") ON [PRIMARY]"
End Function

Public Function ColumnDDL(col As DataColumn, Optional suffix As String = "") As String
    Dim buf As String = "[" + col.ColumnName + "] "
    Select Case col.DataType.Name
        Case "string"
            buf += " varchar(" + col.MaxLength.ToString() + ")"
        Case "integer"
            buf += " int"
        Case "double"
            buf += " float"
        Case Else
            Return ""
    End Select
    If col.AllowDBNull Then
        buf += " NULL"
    Else
        buf += " NOT NULL"
    End If
    Return buf + suffix
End Function
Apparently there's a length limit on posts? So this is continued in the one that follows...

Last edited by mmcginty; 12-27-2012 at 06:56 AM.
Reply With Quote
  #7  
Old 12-27-2012, 05:13 AM
mmcginty mmcginty is offline
Newcomer
 
Join Date: Apr 2009
Location: Central California Coast
Posts: 13
Default some code (continued)...

... Now you could surely generate DML statements from a DataTable as well but the DataAdapter effectively does that for you... Is the objective to be able to dynamically create a table and insert data into it from any xlsx/csv data? Again this is incomplete, you'd need to add support for whatever other data types you'll encounter, but this is a pretty good start... (Edit the connection string to suit your environment.)

Code:
Sub CreateAndInsert(dt As DataTable)
    Dim cn As New SqlConnection("Server=(local);Database=myDatabase;Trusted_Connection=True;")
    cn.Open()
    Dim cmd As New SqlCommand(TableDDL(dt), cn)
    cmd.ExecuteNonQuery()
    Dim adapter As New SqlDataAdapter()
    Dim FieldList As String = "", ValueList As String = ""
    Dim i As Integer
    Dim ColName As String
    Dim ParamName As String
    adapter.MissingSchemaAction = MissingSchemaAction.Ignore
    adapter.InsertCommand = New SqlCommand()
    adapter.InsertCommand.Connection = cn
    For i = 0 To dt.Columns.Count - 1
        ColName = dt.Columns.Item(i).ColumnName
        '' remove a few characters that are illegal in SQL variable nams
        ParamName = "@" + Replace(Replace(Replace(Replace(ColName, " ", ""), "'", ""), "-", ""), "/", "")
        If i > 0 Then
            FieldList += ", "
            ValueList += ", "
        End If
        ' usually the SQL statement and parameter bindings are hard-coded
        ' this dynamic technique would need to be more complex if any columns
        ' were read-only
        FieldList += "[" + ColName + "]"
        ValueList += ParamName
Code:
        Select Case dt.Columns.Item(i).DataType.Name
            Case "string"
                adapter.InsertCommand.Parameters.Add(ParamName, SqlDbType.VarChar, -1).SourceColumn = ColName
            Case "integer"
                adapter.InsertCommand.Parameters.Add(ParamName, SqlDbType.Int).SourceColumn = ColName
            Case "double"
                adapter.InsertCommand.Parameters.Add(ParamName, SqlDbType.Float).SourceColumn = ColName
        End Select
    Next
    adapter.InsertCommand.CommandText = "INSERT INTO [" + dt.TableName + "] (" + FieldList + ") VALUES (" + ValueList + ")"
    Dim row As DataRow
    For Each row In dt.Rows
        row.SetAdded()
    Next
    adapter.Update(dt)
End Sub
To tie it all together...
Code:
 Dim dt As DataTable = DataTableFromXLSX()
 dt.TableName = "MyTable3"
 CreateAndInsert(dt)
The code I posted lacks a number of things, like exception handling of any kind, support for all possible types, and the ability to update to name a few. It also doesn't address the possibility the a table of the specified name might already exist (and as such if you ran this code twice unchanged, it'll fail the second time.)

But I think it accomplishes what I perceived to be your goal in your original post. As for the DataGridView with data maintained by a worker thread... runaway scope creep or what? :-)

Maybe you can clarify the big picture a little, and I'll try to post a WebService project with client in the next day or two.
Reply With Quote
  #8  
Old 12-27-2012, 07:24 AM
mmcginty mmcginty is offline
Newcomer
 
Join Date: Apr 2009
Location: Central California Coast
Posts: 13
Default

Almost forgot to note that even though I have Office 2010 installed, I had to download/install the ODBC drivers from here.
Reply With Quote
  #9  
Old 12-27-2012, 06:43 PM
mmcginty mmcginty is offline
Newcomer
 
Join Date: Apr 2009
Location: Central California Coast
Posts: 13
Default An example project

This project was created in VS2010. Placing a website inside the solution directory was not the default storage, but I think it should work on any system (I believe all paths are relative.) You will have to replace the WebService URL in the client and the connection string in the WebService.

There is one button and one text input in the form, the text input sets the name of the table that will be created from .xls or .xlsx (which must contain a sheet named sheet1.) Clicking the Test button opens the common file dialog, select a valid file and click Ok, and a table will be created. If an error occurs it will be displayed in a MsgBox, otherwise no news is good news.

In case anything goes wrong loading the project, these are the steps to recreate it:
  1. Create a Windows Forms app in a new solution.
  2. Add a blank website project to the solution.
  3. Add a new item to the website project, type: WebService.
  4. Copy the code in ./WebService/App_Code/WebService.vb, build and test in a browser if desired.
  5. Publish the website to your provider.
  6. Add a Service Reference to the form app, click the Advanced button, then click the WebService button; enter the appropriate URL.
  7. Copy the code from form1.vb, change any object names to match those of your choice, as necessary.

Please let me know if you have any problems or questions.
Attached Files
File Type: zip DynamicDataAdapter.zip (57.4 KB, 2 views)
Reply With Quote
  #10  
Old 12-28-2012, 02:09 AM
bokeh bokeh is offline
Regular
 
Join Date: Aug 2012
Posts: 60
Default The difficulty in custom templatizing of CreateTable sql command generation

Quote:
Originally Posted by mmcginty
Is the objective to be able to dynamically create a table and insert data into it from any xlsx/csv data?
Again this is incomplete, you'd need to add support for whatever other data types you'll encounter.
Yes this is the objective.
Yes this is one of the most important "incomplete-ness" difficulties.
--and thanks for the three posts - I know it's a little bit of a hassle to have to work around the forum's post text length limits.

Even being able to grab the table column names from the "header" (first row/line) of a .xlsx or .csv file
doesn't imply an aritificial intelligence about what types (int, nchar, etc)
to use in formatting the necessary CreateTable SQL command.

So there essentailly has to be an interface where,
even if some computer code routine can offer a suggestion for the Transact-SQL data types,
it is the interface user (someone who is hopefully smart enough to make
the ultimate decision..like a developer and/or database administrator),
who must do the final tweaking of the column creation data type parameters:
Is it int or bigint for a column named "serial key"?
Is it nchar 50 or 254 for the column named "comments"?

I don't know of any way you could have a heuristic code routine
that could correctly assess such a database design decision 100% of the time.

In thinking about such an interface I'm thinking it would be fairly easy under VB6.
You could create a large array of text boxes (holding the column names)
drop down combo boxes (holding the possible data types)
and a label with a slider to select character/integer length for the column.

The number of table columns (from the comma separated values of the csv file's first line)
determines how many sets of controls arrays are shown
(using the looping through the control arrays index number and setting
the right number to be visible out of say 50 to 100 created at design time).

Under VB.Net of course things are a bit more complicated/involved.
There are no control arrays creatable at design time with pre-set indexes.

I know DataGridView controls can have embedded drop down combobox controls,
and you could probably create the number of dgv rows at runtime, (representing the number of table columns),
from the comma separated values of the csv file's first line.

I have not been able to find an examples of this though.
Quote:
Originally Posted by mmcginty
In case anything goes wrong loading the project..
As it so happens there were issues trying to load the web service project part of the solution inside your attachment.
(See attached screenshot)
I did have the ODBC drivers installed.
I will try to do the manual re-creation you suggest a little later.

Right now I'm spending hours and hours uploading thousands of rows of data to GoDaddy
(in small batches so the web admin Query Analyzer doesn't time out).

I figured a long-way-around method of converting a csv to
a MsSQL Trasnact-SQL type .sql file (by using a MySQL thru-convert).

First I use this:
Convert CSV to MySQL - Free Online Tool
..which has a nice "First row is column titles" checkbox.

Then I use the MySQL type .sql file this online converter utility creates to create a GoDaddy MySQL database

Note:
You are allowed 25 of MySQL databses under the Windows type Godaddy Web Hosting setup I'm working with.
Personally I think having a MySQL database option (with php supported) under a
Windows web hosting setup (instead of using Linux hosting) is, at first, a bit starnge.

However I think it is actually designed for the GoDaddy hosters who want
to use Windows type web hosting but still have a WordPress setup (maybe?)


Anyway after the MYSQL dataabse is setup under GoDaddy, there is a an option
for exporting from "SQL" (MySQL that is) to MsSQL (see attached screenshot).

Unfortunately if you look at the Transact SQL inside the .sql file that this creates, both the CreateTable SQL commands,
and all the thousands of the INSERT commands (to insert data into a table) are wrong.
How do I know they are wrong?
If you go into the MsSQL databse section and copy/paste the generated SQL commands
into the GoDaddy web admin Query Analyzer none of the SQL works ('unexpected error").

The CreateTable SQL is too far off the mark to even bother trying to correct
(I know this because I know what the format is supposed to be - see example in the first post in this thread).

The Insert commands however can be fixed by manually "merging"
a sample set of working INSERT SQL commands with the non-working ones,
(by way of using Microsoft Word's Find and Replace - which includes
the "special" option of inserting line breaks for ";", an option that NotePad/NotePad++/metapad don't seem to have).

Which just leaves the csv to t-sql CreateTable utility as still possibly needed.
(I manually pieced together a CreateTable SQL command for the GoDaddy Query Analyzer
that actually created a temp MsServer table so I could test "merge-corrected" Insert SQL commands).
Quote:
Edit1:
Also of note I did find (after later experimentation) that the GoDaddy CSV import to a MsServer database does work
if you are very very careful about the creation of the csv files you try to upload.

The first thing to know is no CSV imports unless you already have a table to import into.
So you must be smart enough to use the Query Analyzer to pre-create a table ahead of time,
(I still haven't found a way to create a MsServer database table just using the GoDaddy CSV Import).

Here are the GoDaddy CSV import to MsServer database "rules":
Before using the Save As command to save a csv file from an Excel (xls or xlsx) you must:
1.) Insure that there are no extra empty columns before the column that contains the first desired table column name.
2.) Insure that there are no empty rows (especially between the first row containing the column names and the second row with actual data in it).
3.) Make sure there are no empty cells in any of the rows/colums containing data (causes "NULL" errors).
4.) Make sure there are no cells that have more than one row of data.
Causes the sets of data to exceed the number of columns to insert the data into.
To check this: select the whole Excel spreadsheet, do Format Auto-fit of columns.
After that right click, format all cells, then set alignment horizontal: center vertical: center
and then scroll thru the rows to spot any weirdly mulit-line formatted cells).
5.) For any columns containing large dollar values make sure the numeric formatting is not set to use "," for dollar values over a thousand.
($1,157.50 instead of just $1157.50).

What happens if you do is that Excel makes a csv with a line like
ENG,Number,Description,0,"1,157.50",0,0
See the problem?
It thinks "1,157.50" is two fields (instead of one):
"1,
157.50"

So as a quick double check after csv save as, do a "Find" search for any quotation marks in the csv file,
and if any slipped thru, remove the quotation marks as well as the extra comma in the dollar value.

Lastly -- upload individual csv files in "chunks" where each "chunk" is a set of rows roughly equal to about
an inch of scrollbar horizontal distance (about 400-600 rows).

I've seen it take over a 1000 rows but I've also seen it time out after 100 rows. The 400-600 rows is average during the night hours.

Last edited by bokeh; 12-28-2012 at 03:56 AM.
Reply With Quote
  #11  
Old 01-14-2013, 02:00 PM
bokeh bokeh is offline
Regular
 
Join Date: Aug 2012
Posts: 60
Default A little bit of progress..and trying to move toward a ui with "SmartChoices"

Just to let everyone know I haven't given up on this thread.
I just got distracted for a while.

Quote:
Re: mmcginty code from post #9 of this thread
I was able to get the WebService code working after doing a manual recreation,
however I showed it to my boss at work and he initally said he wasn't sure.

A couple of days later (after talking to a few of the company's IT people I heard)
he came back and said the web service was a "no go".
His tone of voice said - "don't evn bother to ask why", so I didn't.
Thus it's back to my first approach.
I was finally able (after a lot of research and trial/error) to piece together something.

It's very tricky working with a datagridview control that has a runtime generated combobox column.
There's no SelectedIndex property with a ComboBox embedded in a DatagridView combobox column,
so you have to do something weird to get one the drop down items to show as the default (selected) item.

The other tricky thing is that you can't grab the value directly out of the
combobox control's Items as you would a normal combobox control.
Instead it has to referenced indirectly as a cell value..as if the combobox control was somehow not really there.

The attached code, though, doesn't yet have the routine I need - which is something to loop through datagridview cells
and gather the necessary cell contents to piece together
the output string for the CreateTable SQL Command text.

I would also like to get it to the point where the CreateTable textbox would adjust
as the datagridview was having its contents adjust (maybe with some kind of cell event?)

I was also thinking last night there should be a way to get the combobox drop, containing data field types, to
put the most likely candidates at the top of the drop down list.

So if the second row of the .csv file contained a "1" then likely (recommended) data field types might be:
1.) int
2.) byte
3.) boolean
4.) binary

I would want these at the top of the drop down combo controls's drop down list,
with probably "init" set as the default combobox selected value.

Other possible cases included:
It could also be (indeterminately) a "nchar".
But it would unlikely be a "bigint".

There should be a way to sort the data field type list in the drop down so
the most likely candidates would be in top (in a black font).
Then below that would be the "indeterminate" type in dark grey.
Then below that would be the unlikely candidates in a light grey.
I call this ui specialness ""SmartChoices", but how to make it happen with the code behind?

Didn't find much in the way of VB.Net examples of this but this StackOverFlow thread with C# code suggests
that the DrawItem could be used, perhaps in combination with DataGridView.EditingControlShowing event?

This off forum thread has some code along those lines, but I don't know how to
adapt it to have three different colors for different drop down text items.

The main difficulty is dealing with all the cases.
It would probably end up being a long Select...Case statement, but is there another way to do it?
Regex pattern matching perhaps? ..or maybe some kind of class?

Anyway, I haven't been able to find any VB.Net along these lines, so I still need help.

Last edited by bokeh; 01-14-2013 at 02:16 PM.
Reply With Quote
  #12  
Old 01-27-2013, 07:00 PM
bokeh bokeh is offline
Regular
 
Join Date: Aug 2012
Posts: 60
Default An update..I learned something!

While I work to gain enough understanding to tackle the issues in the other thread I have open,
I'd thought I'd return to this thread for an hour today.

The main issue with the attachment to my last post is that changing the datagridview values doesn't change the generated create table text output.
This is mainly because I don't know how to "readback" all the values in the datagridview control in the output text as variable values.

Probably anyone taking a quick look at the screenshot would say:
"Why not just get the values from the datatable that is being used as a datasource."
Unfortunately its not that simple.

While the middle two columns of the datagridview are bound to the data from the TextParser's reading of the csv file values,
the first column (the checkboxes) and the last column (the comboboxes) are unbound (added at runtime after the datagridview binding to the datasource).

So half the columns are bound and half aren't.
It's the unbound combobox column that is giving me the most headaches.

In the last attachment I had this line:
Code:
cmb.DefaultCellStyle.NullValue = cmb.Items(0)
What I thought that line was doing was assigning the first item in the combobox list of drop down items
to be the SelectedIndex item,
(basically having it as the combobox's selected item , if the strange
embedded combobox controls had a SelectedIndex or SelectedItem, which they do not).

I also figured that this was assigning the value to the datagridview cell.
Wrong!

So what does this line do?
The best as I can figure it's kind of like a DrawString.
It "paints" all the cells of the datagridview combobox column with that text string. (see screenshot1)

How do I know that no assignment is being done?
I use this snippet:
Code:
If Not dgv1.Rows(0).Cells(3).Value = Nothing Then
  Dim strText1 As String = dgv1.Rows(0).Cells(3).Value.ToString
  Dim strText2 As String = dgv1.Rows(1).Cells(3).Value.ToString
  Dim strMsg = "For Row 0, Column 3 value = " & strText1 & " and" & vbCrLf & _
  For Row 1, Column 3 value = " & strText2
  MsgBox(strMsg)
End If
..when run the code the message box displays nothing.

I also found if you take out the If..Then it throws an exception:
Quote:
Object reference not set to an instance of an object.
Which is basically "what your trying to get at doesn't exist".

--Yes I did some stepping thru to figure out the If..Then would get rid of the error.


So how to arrive at a solution?
I tried this:
Code:
cmb.DefaultCellStyle.NullValue = "Not a cell value"
..and it happily replaces "nChar" with "Not a cell value"
(see screenshot2)

I thought about this and (after a while) figured:
Quote:
Well if the datagridview claims to have no value then obvious I,
as the programmer in charge of that control, need to assign one manually..
After twenty minutes of research I came up with the necessary extra two lines and added it
to the code section that creates and populates the datagridview at runtime:
Code:
'Create combobox column and populate it from the fields2 string array
cmb = New DataGridViewComboBoxColumn()
cmb.HeaderText = "Select Data Type"
cmb.Name = "cmb"
cmb.ValueMember = "cmb"
cmb.MaxDropDownItems = 8
'Add drop down data to combobox controls in combobox column
For index As Integer = 0 To fields2.GetUpperBound(0)
  If Not (fields2(index) Is Nothing) Then
      cmb.Items.Add(fields2(index))
  End If
Next
'This is seems to be the weird way to set SelectedIndex 
'with combobox controls embedded in combobox columns
'cmb.DefaultCellStyle.NullValue = cmb.Items(0)
cmb.DefaultCellStyle.NullValue = "Not a cell value"
'Now that everything is set, add the combobox column to the datagridview control
dgv1.Columns.Add(cmb)
dgv1.Columns(3).Width = 120
dgv1.Rows(0).Cells(3).Value = "nChar"
dgv1.Rows(1).Cells(3).Value = "nChar"
when I ran that changed code produced the attached screenshot3.

So I commented out the line:
Code:
cmb.DefaultCellStyle.NullValue = "Not a cell value"
..and a little messagebox enabled debugging produced screenshot 4 showing that at last the
datagridview cells containing the embedded combobox controls had a cell value.

Success!

Now I just need to work on getting some kind of value for the checkbox column..



Oh, and for all those who read this, and are still working out how to get the value at the moment that the datagridview's combobox value changes,
I offer this bit of working (tested) code:
Code:
Private Sub dgv1_EditingControlShowing(ByVal sender As Object, _
    ByVal e As System.Windows.Forms.DataGridViewEditingControlShowingEventArgs) Handles dgv1.EditingControlShowing
   Dim cb As ComboBox = CType(e.Control, ComboBox)
   If cb IsNot Nothing Then
      AddHandler cb.SelectedIndexChanged, AddressOf cb_SelectedIndexChanged
   End If
End Sub

Private Sub cb_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
   MessageBox.Show("Value Selected: " & CType(sender, ComboBox).SelectedItem.ToString)
End Sub
Of course if you left your datagridview named as the default "DataGridView1",
then just substitute (replace) "DataGridView1" for "dgv1" in the above code.
Attached Images
File Type: jpg screenshot1_.JPG (48.3 KB, 1 views)
File Type: jpg screenshot2_.JPG (49.5 KB, 1 views)
File Type: jpg screenshot3_.JPG (49.3 KB, 1 views)
File Type: jpg screenshot4_everything_working_right.JPG (60.5 KB, 1 views)

Last edited by bokeh; 01-27-2013 at 07:18 PM.
Reply With Quote
  #13  
Old 01-30-2013, 11:09 AM
bokeh bokeh is offline
Regular
 
Join Date: Aug 2012
Posts: 60
Default Retreiving datagridview checkbox column boolean values for all rows

This code should probably be in the MSDN somewhere but it's not.

Hopefully it will come up easily if someone searchs..
Code:
'Get datagridview control checkbox column values 
'into multiline textbox usng looping through all rows
Private Sub btnReadCheckboxColumnValues_Click(ByVal sender As System.Object, _
     ByVal e As System.EventArgs) Handles btnReadCheckboxColumnValues.Click

    ''This works...but is the 'longhand' version
    'Dim iSselected As Boolean
    'Dim bytCheckBoxColumnIndex As Byte = 0
    'Dim i As Int16
    'With Me.dgv1
    '    For i = 0 To .RowCount - 1
    '        If .Rows(i).Cells(bytCheckBoxColumnIndex).Value IsNot Nothing Then
    '          iSselected = .Rows(i).Cells(bytCheckBoxColumnIndex).Value
    '          If iSselected = True Then
    '            txtCheckboxValues.Text = txtCheckboxValues.Text & _
    '                    "For Row " & i & ": " & _
    '                    "CheckBox Value = " & iSselected & vbCrLf

    '          Else
    '            txtCheckboxValues.Text = txtCheckboxValues.Text & _
    '                    "For Row " & i & ": " & _
    '                    "CheckBox Value = " & iSselected & vbCrLf
    '          End If
    '        End If
    '    Next
    'End With


    ''This works..and is shorter, but skips false values
    'Dim iSselected As Boolean
    'Dim bytCheckBoxColumnIndex As Byte = 0
    'With Me.dgv1
    '    For i As Int16 = 0 To .RowCount - 1
    '        If .Rows(i).Cells(bytCheckBoxColumnIndex).Value IsNot Nothing Then 'bypasses false values
    '          iSselected = .Rows(i).Cells(bytCheckBoxColumnIndex).Value
    '            txtCheckboxValues.Text = txtCheckboxValues.Text & _
    '                    "For Row " & i & ": " & "CheckBox Value = " & iSselected & vbCrLf
    '        End If
    '    Next
    'End With

    'This is even slightly more compact, but still outputs false values
    Dim bytCheckBoxColumnIndex As Byte = 0 'If you have more than 254 columns then use Int16 instead of Byte
    With Me.dgv1 'dgv1 is the name of the DtaGridView, default name for control is DataGridView1
        For i As Int16 = 0 To .RowCount - 1
            Dim IsTicked As Boolean = CBool(dgv1.Rows(i).Cells(bytCheckBoxColumnIndex).Value)
            'Note: txtCheckboxValues.Multiline = True
            txtCheckboxValues.Text = txtCheckboxValues.Text & _
                    "For Row " & i & ": " & "CheckBox Value = " & IsTicked & vbCrLf
        Next
    End With
End Sub
Reply With Quote
  #14  
Old 02-02-2013, 06:23 PM
bokeh bokeh is offline
Regular
 
Join Date: Aug 2012
Posts: 60
Default Up to version 17..

First of all --I found some code on the social.msdn forum threads for dynamically populating a datagridview control,
then creating a csv file from the datagridview cell values,
then reading the csv file and reloading the values back into the datagridview.

Someone else tweaked the LoadCSV sub to make it more efficient.

I wasn't happy with the lack of CSV file validation,
so I found a code snippet on a Codeproject page and worked it into a module -- modCSValidate.vb.

Note: If you want to find trace the code sources (the social.msdn threads urls and the CodePage urls),
then "view" (download) the attachment and look at the comments in the code.

The whole enchilada is rolled into a nice working demo and attached below.
The reason it's nice is that most of the datagridview control loading samples or examples
require some kind of database and or a connectionstring to run.
This demo does not.

It uses thousands and thousands of rows of data so it takes like 5-6 seconds to run (shows elapsed time after it finished).
Not much to show in a screenshot --this demo is strictly to show working code for study.
Enjoy!

The code from my last attachment in post #11 has undergone some made (multiple) re-visions.
I'm planning on tweaking the interface a little bit but before I do I figure I should post where the code is at (what point it's up to) right now.

The most difficult part was integrating the OutputText() sub with the changing (dynamic)
datagridview cell values as well as the database name and table name drop downs:
Code:
Private Sub OutputText() ' Needs to dynamically create text strings from datagridview control contents
    Dim strDBName As String = cboDatabaseNames.SelectedItem.ToString
    Dim strTableName As String = cboTableNames.SelectedItem.ToString
      'MsgBox(strDBName & vbCrLf & strTableName) 'Debug only
    Dim strSQL_Output_AllParts As String, strSQL_Output_Header As String
    Dim strSQL_Output_Footer As String, strSQL_Output_Body As String = Nothing
    If blnLoadComplete = True Then 'datagridview control has loaded data
      ' strDBName and strTableName are loaded from comboboxes (see above dims)
      strSQL_Output_Header = "USE [" & strDBName & "]" & vbCrLf & _
                    "GO" & vbCrLf & _
                    "CREATE TABLE [dbo]." & strTableName & "](" & vbCrLf & _
                    "[" & "ColumnPK" & "] [int] NOT NULL IDENTITY(1,1)," & vbCrLf
      'Dynamic creation of CreateTavle lines based on how datagridview control 
      'is populated (and perhaps later edited) by user
      'In the case where changes are made in the datagridview control
      'after GenerateText button is pushed / clicked, then OutputText() updates automatically 
      Dim strFieldLength As String, strFieldType As String, strColumnName As String
      Dim IsChecked As Boolean
      For i As Int16 = 0 To intColumnsCount - 1
        IsChecked = Me.dgv1.Rows(i).Cells(0).Value.ToString
        If IsChecked = True Then
        strColumnName = Me.dgv1.Rows(i).Cells(1).Value.ToString
        strFieldLength = Me.dgv1.Rows(i).Cells(2).Value.ToString
        strFieldType = Me.dgv1.Rows(i).Cells(3).Value.ToString
        strSQL_Output_Body = strSQL_Output_Body & _
                          "[" & strColumnName & _
                           "] [" & strFieldType & _
                           "](" & strFieldLength & _
                           ") COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL," & vbCrLf
        End If
      Next
      strSQL_Output_Footer = "CONSTRAINT [PK_Inventory] PRIMARY KEY CLUSTERED" & vbCrLf & _
          "(" & vbCrLf & _
          "[Column1](Asc)" & vbCrLf & _
          ")WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, " & _
            "ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)" & vbCrLf & ")"

      strSQL_Output_AllParts = strSQL_Output_Header & _
                            strSQL_Output_Body & _
                            strSQL_Output_Footer
      TextBox1.Text = ""
      TextBox1.Text = strSQL_Output_AllParts
    Else
      MsgBox("Please import CSV data before attempting to generate SQL text." & vbCrLf & _
            "Thank you!!", vbInformation, "Generate Text Instructions")
    End If
End Sub
Maybe some of you hard core coders out there enjoy writing code like that but for me I found it totally no fun at all.
But it had to be done.

The other part was the necessary "trigger" code for the datagridview cell editing
(including editing cells in the column containing those annoying embedded combobox controls):
Code:
'Sub below has code to allow user generated text (at runtime) to be typed in
'that doesn't correspond to any of the drop down values
'then saves it as a new combo drop down item after editing is completed
Private Sub dgv1_CellValidating(ByVal sender As Object, _
                ByVal e As System.Windows.Forms.DataGridViewCellValidatingEventArgs) Handles dgv1.CellValidating
      If blnLoadComplete = True Then
       Dim comboBoxColumn As DataGridViewComboBoxColumn = CType(dgv1.Columns(3), DataGridViewComboBoxColumn)
       If (e.ColumnIndex = comboBoxColumn.DisplayIndex) Then
          If (Not comboBoxColumn.Items.Contains(e.FormattedValue)) Then
          Dim cell As DataGridViewComboBoxCell = DirectCast(dgv1.Rows(0).Cells(3), DataGridViewComboBoxCell)
          If ((Not (cell) Is Nothing) AndAlso Not cell.Items.Contains(e.FormattedValue)) Then
              ' Insert the new value into position 0
              ' in the item collection of the cell
              cell.Items.Insert(0, e.FormattedValue)
              ' When setting the Value of the cell, the string is not shown until it has been comitted. 
              ' The code below will make sure it is committed directly.
              If dgv1.IsCurrentCellDirty Then
                  ' Ensure the inserted value will be shown directly.
                  ' First tell the DataGridView to commit itself using the Commit context...
                  dgv1.CommitEdit(DataGridViewDataErrorContexts.Commit)
              End If
              ' ...then set the Value that needs to be committed in order to be displayed directly.
              cell.Value = cell.Items(0)
            End If
          End If
        End If
        If blnGenerateTextButtonUsed = True Then
          OutputText()
        End If
      End If
End Sub
'The sub below has some code to allow the datagridview control embedded  combobox to be editable
Private Sub dgv1_EditingControlShowing(ByVal sender As Object, _
   ByVal e As System.Windows.Forms.DataGridViewEditingControlShowingEventArgs) Handles dgv1.EditingControlShowing
   If blnLoadComplete = True Then 'test if datagridview control actually populated
     'MsgBox(Me.dgv1.CurrentCell.ColumnIndex) 'Debug only
     If Me.dgv1.CurrentCell.ColumnIndex = 3 Then 'Are we dealing with column index 3?
       If TypeOf e.Control Is ComboBox Then 'Does this column contain an embedded combobox control?
        Dim comboControl As DataGridViewComboBoxEditingControl = CType(e.Control, DataGridViewComboBoxEditingControl)
        If (Not (comboControl) Is Nothing) Then
            ' Set the DropDown style to get an editable ComboBox where the user can type something in..
            If (comboControl.DropDownStyle <> ComboBoxStyle.DropDown) Then
                comboControl.DropDownStyle = ComboBoxStyle.DropDown
            End If
        End If
      End If
     ElseIf Me.dgv1.CurrentCell.ColumnIndex = 2 Then
       'Editing field length value
     ElseIf Me.dgv1.CurrentCell.ColumnIndex = 1 Then
       'Editing column name value
     ElseIf Me.dgv1.CurrentCell.ColumnIndex = 0 Then
       'Changing checkbox value
     End If
   End If
End Sub
This datagridview cell validating code is pretty unique and original.
It's a combination of code from an off forum thread and an article on a site that doesn't exist anymore (had to pull code form the WayBack machine).
Probably not the "perfect" way to code such things but it works.

What I need now is more information on how the structure of the CreateTable command
is subtlely altered when using different combinations of data field types.

For instance, under MySQL there is an option when setting up a data field to have it fill (pad) with zeros.
Of course this only works with number field types (numeric, int, bigint, smallint, tinyint, etc).

There should be a T-SQL chart somewhere --I just have to find it.
It's not on the MSDN Transact-SQL Syntax Conventions page or the MSDN CreateTable page or the MSDN Data Types page.

Maybe such a convenient chart doesn't exist at all but I'll keep looking.

Little later..
Found this page which only says the NULL | NOTNULL might be the only missing designator I need to handle.
Found this MSDN page on SQL-92 compatibility synonyms fo datatypes.
Found this MSDN page for custom user defined data types.
Found this MSDN page which goes into a little bit the rules that SQL Server applies when you use arithmetic operators to perform implicit or explicit conversion (depending on whether the query is autoparameterized or not).

Lastly I should state: with this posting, the thread (and it's attached code revision 17) is 90% there toward its conclusion.

Last edited by bokeh; 02-02-2013 at 07:13 PM.
Reply With Quote
  #15  
Old 02-10-2013, 05:50 PM
bokeh bokeh is offline
Regular
 
Join Date: Aug 2012
Posts: 60
Default CodeCruncher vs. AtmaWeapon, TextParser class peculiarities, and ASCII data encoding

Well, it looks like a bunch of stuff happened while I've been away (since the my last post in this thread.

I found a "kindred spirit" in CodeCruncher.

We had a little 3 post exchange in the Intersecting line thread about AtmaWeapon (1, 2, 3)

This follows on my tangling with AW in the CaesarCipher thread.

CodeCruncher and AtmaWeapon got a chance to go head to head in the "Text Wrap in a combobox" thread.

In post #14 of that thread CodeCruncher said:
Quote:
Would you like your code go to waste, or have someone who understands the fear of being a learner, bridge the gap.
I know you are a very experienced programmer, but you need to understand
that sometimes what might be an elementary basic to you can be terrifying to others.
In post #15, AW replied:
Quote:
I suppose my technical tone can seem harsh..
..then went into a long rationalization /justification.

I do sympathize with AW's comments, but on the other hand
most of the other forum experts seem to have a better understanding
of when they've reached the point where it's better to "let things go"
(when they realize they're not helping) and leave it to others on the forum
to try to establish a better "rapport" (and manner of helping) for those newbies who may be
just a little overwhelmed with AW's brand of engagement.

I'm just glad a have this thread, (my little solitary corner of the XVBT forum),
to try and work through all the coding issues I'm dealing with

Now to the heart of what's been going on offline for me.

Most of the my csv file using attachments in this thread use the TextParser class.
The more and more I work with it the more I really like it.
It's a more "higher order" than the type of File I/O that was typically used under VB6.

After many weeks I finally found out how to do ASCII to Decimal encoding
(both reading and writing to files) using letter by letter approach for a single short word (string).

The way I'm doing the encoding is definitely not the recommended .Net way of encoding
(which uses Base64 or one of the utf type encodings).
So the MSDN has been of limited usefulness,
but I'm learning a lot more about string manipulation techniques.

At this point I was ready to expand my ASCII-to-Dec encoding sample
to encompass multiple lines containing multiple delimited strings.

Since I already had some working code to do this using TextParser
I thought it would easy to rip out that code and merge it into the data encoding sample.

The difficulty I ran into - while the TextParser class does have a LineNumber property it has no in-built LineCount property,
(which would get a count of the number of lines in the csv text file).

I found some line count Stream/Regex code here by a really smart guy named: "jmcilhinney".

I also found some strangeness when it comes to how LineNumber property actually works in practice.
This was sorta noted in this thread, but that thread didn't really have a -1 workaround.

Here is what the MSDN TextFieldParser.LineNumber property page says:
Quote:
Returns the current line number, or returns -1 if no more characters are available in the stream.
No it really doesn't return the current line number (see colored comments in code snippet below).
Yes, you do have to have a way around the "-1" gotcha (thats where knowing the line count comes in handy).

Here's the code snippet (with the workaround for both issues):
Quote:
Code:
Imports System.IO
Imports System.Text
Imports Microsoft.VisualBasic.FileIO

'Not quite the VB6 App.Path but better than the long alternatives
Private Function app_path() As String
  app_path = New System.IO.FileInfo(Application.ExecutablePath).DirectoryName
End Function

'Code attached to button for testing..
Private Sub btnLoadCSV_Click(ByVal sender As System.Object, _
                       ByVal e As System.EventArgs) Handles btnLoadCSV.Click
  Dim intCount As Integer
  Dim Data As String()
  Dim lineCount As Integer = Nothing
  Dim intRealLineNumber As Int16 = Nothing
  Try
    'Declare for filename (and path) variable
    Dim strFilename As String
    strFilename = app_path() & "\test.csv"
    'Show only selected file name (not including path)
    'txtFileName.Text = Path.GetFileName(strFilename)
    'MsgBox(strFilename, vbOKOnly, "Debug Only..")
    If My.Computer.FileSystem.FileExists(strFilename) Then
      'Get line count of csv first because textparser returns "-1" for last line
      Try
       'The 3 lines below are the line count snippet I mentioned above 
        Dim sr As New StreamReader(strFilename)
        lineCount = System.Text.RegularExpressions.Regex.Split(sr.ReadToEnd(), _
                                                Environment.NewLine).Length
        sr.Close()
      Catch ex As Exception
        MsgBox(ex.Message)
      End Try
      'Use TextFieldParser to get comma delimited values from csv file
      'Note: Keyword 'Using" allows for easy automatic garbage disposal
      Using Reader As New Microsoft.VisualBasic.FileIO.
        TextFieldParser(strFilename)
        Reader.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited
        Reader.Delimiters = New String() {","} 'for comma separated value file
          'Dim currentRow As String()
          While Not Reader.EndOfData
            Try
              Data = Reader.ReadFields()
              intCount = Data.Count
              For i As Integer = 0 To intCount - 1
                'After "Reader.ReadFields()" reads in one line, 
                'the textparser class moves to next line so the 
                'LineCounter value for the Reader is always one line ahead
                'of the actual line that has just been read
                'so the "intRealLineNumber" value is used to keep track 
                'of the line that is the actual "current line"
                intRealLineNumber = CShort((Reader.LineNumber - 1))
                If Reader.LineNumber > 0 Then
                  'txtCSVData is textbox where Multiline property = True
                  txtCSVData.Text = txtCSVData.Text & "Line#: " & _
                                  intRealLineNumber & ", " & _
                                  "DataField" & "(" & _
                                   i & "): " & Data(i) & vbCrLf
                Else 'Reader.EndOfData reached
                  'Reader.LineNumber at this point = -1 
                  'because we're at end of file (and last line has already been read)
                  'So instead use linecount value derived above
                    txtCSVData.Text = txtCSVData.Text & "Line#: " & _
                                   lineCount & ", " & "DataField" & _
                                    "(" & i & "): " & Data(i) & vbCrLf
                End If
              Next
            Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
                MsgBox("Line " & ex.Message &
                "is not valid and will be skipped.")
            End Try 'Reading csv fields into string array
          End While 'While not at end of file
      End Using 'Using Reader
    End If 'File Exists
  Catch ex As Exception
      MsgBox(ex.Message)
  End Try
End Sub
I'll attach the full working sample at this point because it also has the working (but non-standard) ASCII encoding as well.

If I can work out how to do splitting lines, splitting values, and splitting 3 character numeric "blocks"
from a special Decimal-To-ASCII encoded file I probably end of tacking it on to the CaesarCipher thread later.

Last edited by bokeh; 02-10-2013 at 06:44 PM.
Reply With Quote
  #16  
Old 03-12-2013, 05:36 PM
bokeh bokeh is offline
Regular
 
Join Date: Aug 2012
Posts: 60
Default Last post

Although this thread is official "dead" I think it could use a "wrap up" post.

There have been at least 3 offline versions developed past the "CSV2Generate_CreateTable_SQL_with_optional_user_selected_data_field_t ypes_ver17.zip" attachment of post#14.

Even though it can now auto-generate the necessary Insert commands in addition to being able to handle
more of the Transact-SQL CreateTable options, it still can not handle all possible permutations for T-SQL table creation.

However at this point it has grown so complex that any hope is gone of boiling it down to a simple "smart choices" wizard because
it has become a "mini" equivalent to Microsoft's SSMS (SQL Server Management Studio) software.

Right now I'm trying to finish up this thread and have begun work on the main Transactions part of the Warehouse Correlator.

At some point I may revisit creating a csv file to transact sql file converter,
but I have to find a format for the utility that is a bit more complicated than a wizard,
yet simpler than a tool which a professional database administrator would use.

Lesson learned:
Microsoft Transact SQL auto-generation is a morass.

At your peril you can skirt around the edges of that vast swamp, just don't try to march straight in,
lest you get bogged down (sucked into the mud) and never be able to work your way out again.

There's a lot of good code for working with csv files (and loading csv data into and out of datagridview controls with validation)
in the other Rk-RS attachment to post #14, so please don't forget to check it out.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
code for local utility: csv to tsql file with create table &amp; insert sql commands
code for local utility: csv to tsql file with create table &amp; insert sql commands
code for local utility: csv to tsql file with create table &amp; insert sql commands code for local utility: csv to tsql file with create table &amp; insert sql commands
code for local utility: csv to tsql file with create table &amp; insert sql commands
code for local utility: csv to tsql file with create table &amp; insert sql commands
code for local utility: csv to tsql file with create table &amp; insert sql commands code for local utility: csv to tsql file with create table &amp; insert sql commands code for local utility: csv to tsql file with create table &amp; insert sql commands code for local utility: csv to tsql file with create table &amp; insert sql commands code for local utility: csv to tsql file with create table &amp; insert sql commands code for local utility: csv to tsql file with create table &amp; insert sql commands code for local utility: csv to tsql file with create table &amp; insert sql commands
code for local utility: csv to tsql file with create table &amp; insert sql commands
code for local utility: csv to tsql file with create table &amp; insert sql commands
 
code for local utility: csv to tsql file with create table &amp; insert sql commands
code for local utility: csv to tsql file with create table &amp; insert sql commands
 
-->