Solved

Command contains unrecognized phrase/keyword when importing into Foxpro table

Posted on 2008-10-27
11
2,172 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
  • 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Article by: Najam
Having new technologies does not mean they will completely replace old components.  Recently I had to create WCF that will be called by VB6 component.  Here I will describe what steps one should follow while doing so, please feel free to post any qu…
Summary: Persistence is the capability of an application to store the state of objects and recover it when necessary. This article compares the two common types of serialization in aspects of data access, readability, and runtime cost. A ready-to…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now