January 12, 2012 at 1:41 pm
I want to add a feature to an internal site - the webpage will allow a user to upload a csv file (not that exciting I know) - it will then display the results of the uploaded source in the web page (like the excel import wizard) and allow the user to answer some questions about the data like which field is primary key etc. Then I want to dump this file into a SQL table (this part I can do). Does anyone know of anything opensource, blog, or tutorial (if you want to share something you have done)?
<Edit> Oh btw...I looked on the web before asking this question and maybe I searched incorrectly -- even some proper search phrases would be appreciated. I looked for "C# example upload display data" - "C# example programmatically add view data like excel wizard" -- Basically I searched things like this and many different search strings if you have a better one that would be sweet too. <Edit>
Thanks in advance,
Eric
January 12, 2012 at 1:56 pm
Not sure which web technology you are using but this is going to be pretty challenging. Probably more difficult than it seems like it should be.
The challenge is that a webserver cannot read files on a remote machine. What this means is that this process will be multiple steps and likely clunky at best. The entire file has to be uploaded to server before the server can open it for reading.
Users will have to wait until the entire file is uploaded before you can start parsing it to dynamically present a UI that will let the users do things like specify the key. You will probably need to allow for composite keys, column renaming etc. Maybe even the option that the first row contains column names etc. Depending on your dev environment I wouldn't be surprised if there is a 3rd party tool can do this either as opensource or a nominal cost. I am interested to hear what others might have to say on this topic.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 12, 2012 at 2:00 pm
I am in a stricly .net shop -- and i forgot to mention that the column names are in the csv files, so that is already there.
January 12, 2012 at 2:06 pm
That certainly removes several of the "what ifs". I think I might approach it as a multi step (page) process. Have your upload page, this page would have your html upload and whatever other options you want. Then when the upload completes you could redirect to ProcessCSV.aspx or something. This page would then be able to read the uploaded file and present a dynamic view. Should be easy enough with a datagrid. Just read your csv into a datatable and bind your grid.
This is pretty intriguing. I am an asp.net guy myself primarily. I have a meeting momentarily but I will check back in tomorrow morning.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 12, 2012 at 2:09 pm
Just saw the edit to your OP. I don't know what the best search phrases would be either, or even how to explain this process in 2-3 words. I am pretty certain this can be done without too much trouble.
Maybe you just use a callback panel so that you can hide/show certain page elements. Do you use any 3rd party client tools?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 13, 2012 at 8:57 am
We use RadControls. The design you spoke of was my basic design. Upload, I thought about kicking off some type of SSIS package programmatically making a table in a stagging database, then showing the table results in an gridview inside an update panel. I did not know if this was a good way to do this or not...but your other post gives me confidence that this might be the right thing to do.
Unless of course someone else thinks this is bad design and can give me some direction somewhere else.
January 13, 2012 at 9:01 am
I don't think you need to go to all the trouble of an SSIS package. Just make data connection direct to your uploaded csv (assuming is is Excel) or just read it into a stream. Then you can do all your manipulation right in memory.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 13, 2012 at 9:12 am
Ok great thanks for the advice.
January 13, 2012 at 9:39 am
I've done something similar using a mishmash of ASP, SSRS and HTML. The BASIC, BASIC steps you need (and you'll need a combination of these) are:
1. Create a database and tables in the desired structure (at least 1NF) that matches the columns in the CSV fields - hopefully, known!
2. Design HTML page with input fields inc. upload via browser function (no help from me on this one I'm afraid).
3. Once CSV is selected, uploaded, use BULK INSERT together with a format file (created using bcp.exe). Example:
At command line:
bcp databaseName..tableName format nul -c -t, -f formatFile.fmt -T
Inside your ASP page, send the following query to SQL Server:
BULK INSERT dbo.targetTable
FROM N'C:\path\file.csv'
WITH (
FIELDTERMINATOR = ',',
KEEPNULLS,
ROWTERMINATOR='/n',
FORMATFILE='C:\path\formatFile.fmt,
KEEPIDENTITY
)
3. In a different function (called via a button, perhaps?) do something like:
SELECT var1, var2 ... varN
FROM dbo.targetTable
WHERE ....
and use your ASP.NET language of choice (mine is VB.NET) to put the output into variables in ASP.
4. Display those variables on your HTML page.
I've created a blog post about connecting web forms to SQL Server at a novice/intermediate level, it helps though if you can code ASP / HTML (which I'm not good at). Here ->
http://uksqldba.blogspot.com/2011/12/connecting-web-form-to-sql-server-easy.html (sorry about the spam!)
---
Note to developers:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
January 13, 2012 at 9:51 am
Wow thanks that should get me well on my way -- i think i may have some issues with the known column names they want to analyze different data and I will never know what it is....but i think i can work around this by obfuscating the end table to just be a table of columns then allow them to name them and drop the data in new named table with named columns. I like your blog post too. Thank you.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply