 |
 |
|
|
|
|
 |
|
|
Samples and other shared info.
| You are not authorized to post a reply.
|
|
| Author |
Messages |
|
pauldes
Posts:1392
 |
| 09/02/2006 6:57 PM |
 Alert
|
This example takes a CSV file and imports the data into a table in the database and uploads that file, stores it in the root directory for the current portal and prefixes the file name with a unique identifier to prevent duplicated file uploads.
Files in CarImport.zip
CreateCarsTable.SQL - USE ONLY IF YOU HAVE NOT INSTALLED THE MyFirstListX sample in this forum. Run this script with a query tool or from HOST>SQL, to install the application table to your DNN database.
CarImport.xml - the ListX module. Add a new page to your site. Add a ListX module. From the module menu choose View Options. Click the XML icon. Replace ALL text in the XML text field with the contents of this file. Click the LOAD link. Click the SAVE CONFIGURATION link.
CarImport.CSV - CSV file to import on the page with the CarImport ListX |
Attachment: CarImport.zip
|
ListX....makes you look brilliant, even though you're not. |
|
|
tiggerz5
Posts:14
|
| 09/09/2006 4:28 PM |
Alert
|
Perhaps it's me, but I think there is something strange going on with Listx for this type of functionality. Try changing the csv file caryear to car year (with a space in between) Then make the appropriate adjustment in the column mapping and SQL. Then run the import. No records are added. If you then go into the CSV file and put [] around car year and then also in the column mapping and SQL then it will work.
BUT... if you create the database table first and it has spaces in the Column Names and the csv source file does not contain brackets around the column headings, as with most flat file downloads the import into Listx does not work. Also, I've tried having the import work based off of position for a multi-column insert and that did not function either.
|
|
|
|
|
tiggerz5
Posts:14
|
| 09/10/2006 12:01 AM |
Alert
|
Pauldes,
Do you do any consulting? |
|
|
|
|
pauldes
Posts:1392
|
| 09/10/2006 9:05 AM |
Alert
|
| Not right now.....but BIFORCE does. I actually use them in our company. I'd recommend based on past experience. |
|
ListX....makes you look brilliant, even though you're not. |
|
|
tiggerz5
Posts:14
|
| 09/10/2006 5:20 PM |
Alert
|
Yes, unforutanely there are quite busy at this time for such minor projects as what I'm doing right now.
Though I did finally get a resolution to my issue. Spaces preceeding a column name which occurs from a downloaded csv file wreak havoc on Listx. I went the other route and used positional formatting which for an 86 column file is a little laborious, but it worked!.
For all the people tracking or submtting this type of issue, there is nothing in the docs on using Position. So... here it is...
The INSERT statement looks like this:
INSERT INTO cars
Values(0,1,2,@var,@var2)
There is no need to place any Column Names on the top line. Just the values in the bottom line. Also, the column mappings must be made just as they would with the Column name. Just put the position as zero based as listed in the docs so the first column is 0, the second actual column is 1, the 3rd column is 2, etc...
Now, to just figure out how to display whether records were added or not and if so, how many.
Thanks for all your work and samples. I have run them all.
Cheers.
|
|
|
|
|
jepper
Posts:216
|
| 09/13/2006 11:31 PM |
Alert
|
I am having problems getting an import to work. I have created a ListX module exactly the way Paul's example does. My CSV file does not contain Column Names on the first row so I used Column values 0-12 for the SQL mapping. When I import the CSV file and look at the ACTIONS debug I receive the following error.
File: Source Variable: <Form> frmVFile. DestinationSQL: INSERT INTO BWP_VehicleImport
(STOCKNO,CARLINE,MAKE,YEARMODEL,COLOR,MILEAGE,ASKINGPRICE,VEHICLESTATUS,VIN,ENTRYDATE,DEALERPRICE,BODYSTYLE,ENGINE)
VALUES
(@STOCKNUM,@CARLINE,@MAKE,@YEARMODEL,@COLOR,@MILEAGE,@ASKINGPRICE,@VEHICLESTATUS,@VIN,@ENTRYDATE,@DEALERPRICE,@BODYSTYLE,@ENGINE)
Undefined
Executing
CSV Import (Query): INSERT INTO BWP_VehicleImport
(STOCKNO,CARLINE,MAKE,YEARMODEL,COLOR,MILEAGE,ASKINGPRICE,VEHICLESTATUS,VIN,ENTRYDATE,DEALERPRICE,BODYSTYLE,ENGINE)
VALUES
(@STOCKNUM,@CARLINE,@MAKE,@YEARMODEL,@COLOR,@MILEAGE,@ASKINGPRICE,@VEHICLESTATUS,@VIN,@ENTRYDATE,@DEALERPRICE,@BODYSTYLE,@ENGINE)
Importing Line - "C60501,MALIBU,CHEVROLET,2000,BRONZE,75955,8050,1,1G1NE52J3Y6280119,9/9/2006,4050,4 DOOR SEDAN,3.1L V6 GAS"
Executing: INSERT INTO BWP_VehicleImport
(STOCKNO,CARLINE,MAKE,YEARMODEL,COLOR,MILEAGE,ASKINGPRICE,VEHICLESTATUS,VIN,ENTRYDATE,DEALERPRICE,BODYSTYLE,ENGINE)
VALUES
(@STOCKNUM,'MALIBU','CHEVROLET','2000','BRONZE','75955','8050','1','1G1NE52J3Y6280119','09/09/2006','4050','4 DOOR SEDAN','3.1L V6 GAS')
CSV Import Failure - System.Data.SqlClient.SqlException: Must declare the variable '@STOCKNUM'.
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Bi4ce.Modules.xList.MessageActions.Handle_File_CSV_to_SQL(DataSet& sharedds, MessageActionItem act, Debugger& Debugger, Stream& Source, Boolean FirstRowIncludesColumnNames, MessageAction_File_ColumnMappings& Mappings, String RecordQuery, ThreadedMessageActionProcess threadObj)
Can anyone explain why all the column values from the CSV are getting transfered except the first one? As you can see the value is there but the Target Value Name is placed at the first value instead of the value from the CSV. All the rest of the values are transfered correctly.
Thanks James |
|
|
|
|
tiggerz5
Posts:14
|
| 09/14/2006 4:22 AM |
Alert
|
James,
Make sure that you have added a mapping to designate column 0 (as the Name not position) to the variable @StockNo. Also verfiy the data type of the STOCKNO filed in your database. Is it set to text, char, varchar, nchar, nvarchar or numeric/int? From the values listed "C60501" it should be one of the former datatypes and not numeric or int.
Now if only someone could tell me how to implement the processName.Status call so I could display the percentage or number of records imported I'd be finished.
Mike |
|
|
|
|
jepper
Posts:216
|
| 09/14/2006 11:02 AM |
Alert
|
Mike thanks for the reply. My CSV file does not have Column Names on the first row. It was my understanding from the docs that you have to use Position Value if your first row does not contain Column Names. I have tried assigning names to all the columns and this did not work either.
James |
|
|
|
|
pauldes
Posts:1392
|
| 09/14/2006 1:37 PM |
Alert
|
Mike, try this. This assumes that withing the Actions you perform for the import, you FIRST set a session variable called ImportInstance and assign it a value of r[ImportInstance,Session]. Then with you FILE action doing the import, check the Run As Process, then give the process a name of Import[ImportInstance,Session].
{IIF,"[Import[ImportInstance,Session].Percent,Action]>0","
<DIV style=\"margin-left: 20px; margin-right: 20px; width: 100%; border: 1px dotted #ededed;\"><img src=\"http://[ALIAS]/images/statusbar.gif\" style=\"width: [Import[ImportInstance,Session].Percent,Action]%; height: 12px; border: 1px solid black;\"></DIV>
<DIV style=\"width: 100%; color: #999999;\"><center>Imported [Import[ImportInstance,Session].Status,Action] rows.</center></DIV>
",""} |
|
ListX....makes you look brilliant, even though you're not. |
|
|
jepper
Posts:216
|
| 09/15/2006 10:28 AM |
Alert
|
There must be a bug when trying to map Column Position 0 to a target. When I have all the columns mapped by position starting with 0 the import does not work. All the fields except for column 0 get converted correctly as you can see from the my post above. The @STOCKNUM value was mapped to column 0 but was translated as the Target Name. I could not figure out any way around this so I added Field Names to the first row of my CSV file and mapped the fields by them and all worked as it should. Did have to change the database datatypes to accept decimal values instead of money data type. Unless someone can tell me how to convert a mapped field to a money data type?
A question for Paul? If I try removing the Actions for importing the file to the server (Actions 2,3,4) and just have the Import to database (Actions 0,1,5,6) from your example it won't import anything when I put them back in it works fine. Do you have to import the file to the server for this to work?
James |
|
|
|
|
kevinmschreiner
Posts:749
|
| 09/15/2006 11:31 AM |
Alert
|
Hey guys, just want to chime in here. I located a bug in the import CSV code that is causing a big headache on your parts which looks to have been introduced in the 1.8.0 version. I am patching this as well as added a few new features to the Query Variable security area. Expect to see a release sometime tomorrow. The problem actually is a bug within the .net StreamReader object. For any of you techies out there - here is what happened:
File Data:
ColumnA,ColumnB,ColumnC
1,2,3
4,5,6
Code: (sReader is a StreamReader object)...
str = sReader.ReadLine
sReader.BaseStream.Position=0
str = sReader.ReadLine
There are a few steps between the initial read and the reset of the position, used for parsing out the Column names, but this is the general code. You would expect that the end result would be:
str = ColumnA,ColumnB,ColumnC
However, it was actually coming out like this:
str = 1,2,3ColumnA,ColumnB,ColumnC
I belive the bug is in the handling of the read buffer, but I'm not 100% certain. Either way, I've corrected the problem and you will have the revised version 1.8.1 shortly.
Thanks for the ongoing support of our product!
|
|
Kevin M Schreiner
Business Intelligence Force, Inc. (bi4ce)
 |
|
|
kevinmschreiner
Posts:749
|
| 09/15/2006 11:33 AM |
Alert
|
| Just one additional note: The new version corrects another issue when dealing with Decimal and Numeric formats. The end result of the query will automatically place single quotes around text values, but will now leave the single qoutes out when the result is numeric. I discovered this as a problem when developing a ListX Reporting mechanism to display the SnowCovered sales data results. If anyone is interested in this ListX Configuration, shout! It provides the CSV Import, and three reports. Summary (By Package), Summary (Package Detail), Detail. |
|
Kevin M Schreiner
Business Intelligence Force, Inc. (bi4ce)
|
|
|
tiggerz5
Posts:14
|
| 09/15/2006 11:46 AM |
Alert
|
Kevin,
Thanks for your monitoring and input. I would greatly appreciate the Listx Configuration offered. Thanks.
Mike |
|
|
|
|
jepper
Posts:216
|
| 09/15/2006 11:59 AM |
Alert
|
Thanks for letting me know it wasn't just me.
James |
|
|
|
|
tiggerz5
Posts:14
|
| 09/15/2006 12:59 PM |
Alert
|
Thanks Paul,
I'll give your snippet a shot tonight thanks very much for the help. I do have a CSV file upload working but no status is provided. |
|
|
|
|
tiggerz5
Posts:14
|
| 09/16/2006 2:39 AM |
Alert
|
Paul,
I don't think I'm implementing your suggestion correctly. The following is a cut and paste of the Actions screen. Any chance of you providing an XML package of file of your example?
Thanks,
Mike
0. Assignment: Assign <Session> variable 'ImportInstance' to '{IIF,"[Import[ImportInstance,Session].Percent,Action]>0"," <DIV style=\"margin-left: 20px; margin-right: 20px; width: 100%; border: 1px dotted #ededed;\"><img src=\"http://[ALIAS]/images/statusbar.gif\" style=\"width: [Import[ImportInstance,Session].Percent,Action]%; height: 12px; border: 1px solid black;\"></DIV> <DIV style=\"width: 100%; color: #999999;\"><center>Imported [Import[ImportInstance,Session].Status,Action] rows.</center></DIV> ",""}'.
1. If '[frmImport,Form]' = 'Import'
2. Execute Query[gd]: Select CAST(GETDATE() as float) as uniident
3. Assignment: Assign <Action> variable 'aUniIdent' to '[uniident,gd]'.
4. File: Source Variable: <Form> frmFile. DestinationPath[PORTALPATH,System]CBUD/[aUniIdent,A].[frmFile.Name,Form]
5. File: Source Variable: <Form> frmFile. DestinationSQL: INSERT INTO sqlmaster.SMAXProjects VALUES('' ,'' ,'' ,'' ,@vTaskNumber,@vTaskName,'','','','',@vContractType,'','','','','','','','','','','','','',@vTaskStatus,'','','','','','','','','','' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'' ,'','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','') Undefined
6. Assignment: Assign <Action> variable '[aUniIdent.Status,action]' to Nothing.
7. Message: Awaiting incoming message with Type 'asdfasd'.
8. Assignment: Assign <Action> variable '[aUniIdent.Complete,action]' to Nothing.
|
|
|
|
|
pauldes
Posts:1392
|
| 09/16/2006 7:51 AM |
Alert
|
It's someone else's code. I would like to check with them before posting.
Right off the bat, I guess I wasn't clear. The IIF/HTML code I posted is supposed to be in a seperate ListX on the page in the No Query section of the List. All assignments of ImportInstance,Session for the process name and the session variable occur in the ListX where the import is happening. As the process is progressing, the ListX with the progress bar is refreshing with the .Percentage.
Also, you need to set the progress bar Listx to enable Ajax, Ajax Interaction - Custom Status, and set the module refresh to something like 1000 milliseconds. Sorry for the half baked instructions. |
|
ListX....makes you look brilliant, even though you're not. |
|
|
jepper
Posts:216
|
| 09/23/2006 11:51 PM |
Alert
|
| Kevin it's been a week how much longer before we see the fix you mentioned? |
|
|
|
|
tthayer
Posts:1
|
| 10/22/2006 9:12 AM |
Alert
|
Hello Kevin,
I have been searching the forums to try and find an answer to the exact problem that James has mentioned and found this post. I just purchased your module last night and was wondering if version 1.8.1 is going to be available soon as this functionality is a major need of mine.
Thanks,
Tom |
|
|
|
|
jepper
Posts:216
|
| 10/22/2006 8:24 PM |
Alert
|
Kevin it's been well over a month since you said it would be the next day. Could you give us an idea when it will be?
|
|
|
|
|
|
| You are not authorized to post a reply. |
|
|
|
ActiveForums 3.6
|
|
| Latest Post
|
 |
|
At R2integrated (formerly Bi4ce), we take support seriously. That's why we support our customers and DNN community with daily monitoring from our experienced engineering team. We ask that the first step taken is to read the relevant documentation and support forums prior to submitting any questions that may already be available or have been answered. We ask that you review the documentation that we provide for our products before posting a question.
The Forums are for our customers to chat, exchange ideas and strategies, and submit feedback. Please be sure to perform keyword searches for previous related forum responses.
To be helpful when submitting a new item, please include the following:
- DNN Version
- Module Version
- Admin Log Viewer Information
- Environment detail: Operating system, .NET framework version, database and version, IIS version, Browser version (if appropriate)
We always try to respond quickly and monitor the forums daily during business hours (EST). Occasionally, requests for a specific project requirement may not apply for the free support offered. For project specific support please submit via our Information Request form.
Thank you for using our Forums.
Click here to register for the Forums
|
|
|
|
| © 2008 by R2integrated (formerly Bi4ce) | DNN® is a registered trademark of DotNetNuke Corporation
|
 |
|
|
|
 |
|