Solved

Command contains unrecognized phrase/keyword when importing into Foxpro table

Posted on 2008-10-27
11
2,230 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

825 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