Solved

Command contains unrecognized phrase/keyword when importing into Foxpro table

Posted on 2008-10-27
11
2,343 Views
Last Modified: 2012-06-27
I am trying to import a CSV into a foxpro table using C# but I keep getting Command contains unrecognized phrase/keyword when I try to exectute

Here is my code, I dont know if I am doint the whole getting data from the CSV right or not


var D1="c1,c2,c3,c4,code,desc,glcomp,n1,n2,n3,rule,table,company,tccomp,ud1,ud2,ud3";
 
 
                foxCmd = new OleDbCommand("INSERT INTO hrtables(" + D1 + ")SELECT " + D1 + " FROM[Text;DATABASE=C:\abra;].[Import.txt])", foxConn);
                foxCmd.CommandType = CommandType.Text;
 
                foxReader = foxCmd.ExecuteReader();

Open in new window

0
Comment
Question by:nemesis1210
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 17

Expert Comment

by:ramrom
ID: 22811560
If [Text;DATABASE=C:\abra;].[Import.txt] is literally passed on to VFP as part of the SELECT FROM clause that is probably the problem.

Or does C# do some magic on that?
0
 
LVL 2

Author Comment

by:nemesis1210
ID: 22811586
if that is the problem how am I supposed to do it?
0
 
LVL 17

Expert Comment

by:ramrom
ID: 22811655
1) What are you using for a connectionString?

2) It is hard to tell from [Text;DATABASE=C:\abra;].[Import.txt] what your goal is.

Please describe it as thoroughly and clearly as possible.

0
Tutorials alone can't teach real engineering

So we built better training tools.

-Hands-on Labs
-Instructor Mentoring
-Scenario-Based Tests
-Dedicated Cloud Servers

All at your fingertips. What are you waiting for?

 
LVL 17

Expert Comment

by:ramrom
ID: 22811659
Is Import.txt a VFP table? If not, what is its format or structure?
0
 
LVL 2

Author Comment

by:nemesis1210
ID: 22811756
Import.txt is a csv file which I want to import into hrtables.dbf connection string is below
foxConn.ConnectionString = @"Provider=vfpoledb.1;Data Source=C:\ABRA\;Collating Sequence=general;";
                foxConn.Open();

Open in new window

0
 
LVL 17

Expert Comment

by:ramrom
ID: 22812333
You can only use SQL SELECT to read from a VFP table. Not from a CSV file.

In VFP we use APPEND FROM import.txt TYPE CSV. Unfortunately one can't use that command thru VFPOLEDB.

Do you have VFP available?

Does C# offer any utilities for reading CSV files?
0
 
LVL 14

Expert Comment

by:tusharkanvinde
ID: 22813755
You can try to use EXECSCRIPT to run the commands

foxconn.Execute("EXECSCRIPT[USE C:\abra\hrtables shared]")
foxconn.Execute("EXECSCRIPT[APPEND FROM C:\abra\import.txt TYPE CSV]")

0
 
LVL 17

Assisted Solution

by:ramrom
ramrom earned 250 total points
ID: 22814062
When I try the 2nd line I get error: Feature not available.
0
 
LVL 14

Expert Comment

by:tusharkanvinde
ID: 22815443
Ramrom

Yes. you are right. All commands are not included in the OLEDB driver. APPEND FROM seems to be one of those that has not been included.

nemesis1210

Sorry. The execscript I gave is not going to work.
0
 
LVL 2

Assisted Solution

by:cthulthu
cthulthu earned 250 total points
ID: 22816532
After you create your connection string, foxConn, can you run SQL commands against the tables to get information from them?  For instance, in FoxPro, I could do the following sequence:

foxConn = sqlconnect(<data source name>)
nResult = sqlexec("select * from hrtables", foxConn, cursHRTABLES)
sqldisconnect(foxConn)

and get a cursor result of the contents of the table.  While connected, I could also perform SQL INSERTS or UPDATES to the table as well.

Is there a similar set of commands for C# to execute SQL against a database connection?  If so, you can read the contents of your TXT file into memory (like an array), and loop through them, creating the appropriately formed SQL INSERT commands line-by-line and executing them.

Also, do you have to use OLEDB?  Is it possible to use an ODBC connection?
0
 
LVL 2

Accepted Solution

by:
nemesis1210 earned 0 total points
ID: 22820187
Thanks for your input all, however due to time restraints on this project I was exploring multiple possible solutions and managed to achieve the desired result using Visual Foxpro 9
0

Featured Post

The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Ivo
C# And Nullable Types Since 2.0 C# has Nullable(T) Generic Structure. The idea behind is to allow value type objects to have null values just like reference types have. This concerns scenarios where not all data sources have values (like a databa…
In order to hide the "ugly" records selectors (triangles) in the rowheaders, here are some suggestions. Microsoft doesn't have a direct method/property to do it. You can only hide the rowheader column. First solution, the easy way The first sol…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question