🚀 go-pugleaf

Connect to Access database and execute:

INSERT INTO MyTable(Col1, Col2)
SELECT Col1, Col2
FROM [Sheet1$] IN ""[Excel 8.0;Database=c:\myxls.xls]

[Sheet1] - named diapason, [Sheet1$] - sheet (if it does not work, try 
[$Sheet1]), [Sheet1$B5:C10] - cells range.

//------------------------------------------
Regards,
Vassiliev V. V.
http://www.managed-vcl.com - using .Net objects in Delphi for Win32 +
ADO.Net
http://www.oledbdirect.com - The fastest way to access MS SQL Server,
MS Jet (Access) and Interbase (through OLEDB)


"Tomislav Hundric'" <rifnet@gmail.com> �������/�������� � �������� 
���������: news:46dae1d4$1@newsgroups.borland.com...
> Please help,
> I need to populate database table from excel file. My database table has 
> same fields as excel file has columns. I use ADO connection to connect to 
> database. Can anyone give me some guide lines or example how to do this.
>
> Thanks in advance. 

First of all thank you on your answer and sorry for my late response,
I'm using ADOConnection and ADOQuery, so I've tried to enter code you've
posted to SQL of ADOQuery, but I can't seem to make connection to excel 
file.

using your code:

 > INSERT INTO MyTable(Col1, Col2)
 > SELECT Col1, Col2
 > FROM [Sheet1$] IN ""[Excel 8.0;Database=c:\myxls.xls]

and changing ADOQuery1->Active = false to ADOQuery1->Active = true

I get error: "Parameter object is improperly defined. Inconsistent or 
incomplete information was provided."

First I thought quotation marks were misplaced, but I've tried every 
possible position without success.


I get the syntax, but don't know what to define in from clause, do I 
need to add something else like :
FROM [Sheet1$] IN ['Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing.xls;']

Thanks in advance,
Tomislav.


Vassiliev V. V. wrote:
> Connect to Access database and execute:
> 
> INSERT INTO MyTable(Col1, Col2)
> SELECT Col1, Col2
> FROM [Sheet1$] IN ""[Excel 8.0;Database=c:\myxls.xls]
> 
> [Sheet1] - named diapason, [Sheet1$] - sheet (if it does not work, try 
> [$Sheet1]), [Sheet1$B5:C10] - cells range.
> 
> //------------------------------------------
> Regards,
> Vassiliev V. V.
> http://www.managed-vcl.com - using .Net objects in Delphi for Win32 +
> ADO.Net
> http://www.oledbdirect.com - The fastest way to access MS SQL Server,
> MS Jet (Access) and Interbase (through OLEDB)
> 
> 
> "Tomislav Hundric'" <rifnet@gmail.com> �������/�������� � �������� 
> ���������: news:46dae1d4$1@newsgroups.borland.com...
>> Please help,
>> I need to populate database table from excel file. My database table has 
>> same fields as excel file has columns. I use ADO connection to connect to 
>> database. Can anyone give me some guide lines or example how to do this.
>>
>> Thanks in advance. 
> 
> 

Set ParamCheck to false. Otherwise it treats :anything as parameter, in this 
case c:\myxls.xls. Use also ExecSQL - this query does not return any data.

//------------------------------------------
Regards,
Vassiliev V. V.
http://www.managed-vcl.com - using .Net objects in Delphi for Win32 +
ADO.Net
http://www.oledbdirect.com - The fastest way to access MS SQL Server,
MS Jet (Access) and Interbase (through OLEDB)


"Tomislav Hundric'" <rifnet@gmail.com> �������/�������� � �������� 
���������: news:46dc2b02@newsgroups.borland.com...
> First of all thank you on your answer and sorry for my late response,
> I'm using ADOConnection and ADOQuery, so I've tried to enter code you've
> posted to SQL of ADOQuery, but I can't seem to make connection to excel 
> file.
>
> using your code:
>
> > INSERT INTO MyTable(Col1, Col2)
> > SELECT Col1, Col2
> > FROM [Sheet1$] IN ""[Excel 8.0;Database=c:\myxls.xls]
>
> and changing ADOQuery1->Active = false to ADOQuery1->Active = true
>
> I get error: "Parameter object is improperly defined. Inconsistent or 
> incomplete information was provided."
>
> First I thought quotation marks were misplaced, but I've tried every 
> possible position without success.
>
>
> I get the syntax, but don't know what to define in from clause, do I need 
> to add something else like :
> FROM [Sheet1$] IN ['Microsoft.Jet.OLEDB.4.0',
> 'Excel 8.0;Database=C:\testing.xls;']
>
> Thanks in advance,
> Tomislav.
>
>
> Vassiliev V. V. wrote:
>> Connect to Access database and execute:
>>
>> INSERT INTO MyTable(Col1, Col2)
>> SELECT Col1, Col2
>> FROM [Sheet1$] IN ""[Excel 8.0;Database=c:\myxls.xls]
>>
>> [Sheet1] - named diapason, [Sheet1$] - sheet (if it does not work, try 
>> [$Sheet1]), [Sheet1$B5:C10] - cells range.
>>
>> //------------------------------------------
>> Regards,
>> Vassiliev V. V.
>> http://www.managed-vcl.com - using .Net objects in Delphi for Win32 +
>> ADO.Net
>> http://www.oledbdirect.com - The fastest way to access MS SQL Server,
>> MS Jet (Access) and Interbase (through OLEDB)
>>
>>
>> "Tomislav Hundric'" <rifnet@gmail.com> �������/�������� � �������� 
>> ���������: news:46dae1d4$1@newsgroups.borland.com...
>>> Please help,
>>> I need to populate database table from excel file. My database table has 
>>> same fields as excel file has columns. I use ADO connection to connect 
>>> to database. Can anyone give me some guide lines or example how to do 
>>> this.
>>>
>>> Thanks in advance.
>> 

Thanks again on your help, I faced problems once again, I'm sorry but 
the following is giving me a headache:

Access database:
     ID (AutoNumber)
     Field1 (Text)
     Field2 (Text)

ADOQuery1
     ParamCheck=false
     Parameters
        Column1
        Column2
     SQL
        INSERT INTO Test (Field1, Field2)
        SELECT Column1, Column2
        FROM [Sheet1$] IN ""[Excel 8.0;Database=c:\testing.xls]

Excel file:
     Column1	Column2
     1.Test11	Test21
     2.Test12	Test22
     3.Test13	Test23

Form
void __fastcall TForm1::Button1Click(TObject *Sender)
{
ADOQuery1->ExecSQL();
}

So everything goes well, but when I open database file in access, there 
are two empty rows created. I don't know why there are two (not three) 
rows created and why are they empty.

Thanks in advance,
Tomislav.



Vassiliev V. V. wrote:
> Set ParamCheck to false. Otherwise it treats :anything as parameter, in this 
> case c:\myxls.xls. Use also ExecSQL - this query does not return any data.
> 
> //------------------------------------------
> Regards,
> Vassiliev V. V.
> http://www.managed-vcl.com - using .Net objects in Delphi for Win32 +
> ADO.Net
> http://www.oledbdirect.com - The fastest way to access MS SQL Server,
> MS Jet (Access) and Interbase (through OLEDB)
> 
> 
> "Tomislav Hundric'" <rifnet@gmail.com> �������/�������� � �������� 
> ���������: news:46dc2b02@newsgroups.borland.com...
>> First of all thank you on your answer and sorry for my late response,
>> I'm using ADOConnection and ADOQuery, so I've tried to enter code you've
>> posted to SQL of ADOQuery, but I can't seem to make connection to excel 
>> file.
>>
>> using your code:
>>
>>> INSERT INTO MyTable(Col1, Col2)
>>> SELECT Col1, Col2
>>> FROM [Sheet1$] IN ""[Excel 8.0;Database=c:\myxls.xls]
>> and changing ADOQuery1->Active = false to ADOQuery1->Active = true
>>
>> I get error: "Parameter object is improperly defined. Inconsistent or 
>> incomplete information was provided."
>>
>> First I thought quotation marks were misplaced, but I've tried every 
>> possible position without success.
>>
>>
>> I get the syntax, but don't know what to define in from clause, do I need 
>> to add something else like :
>> FROM [Sheet1$] IN ['Microsoft.Jet.OLEDB.4.0',
>> 'Excel 8.0;Database=C:\testing.xls;']
>>
>> Thanks in advance,
>> Tomislav.
>>
>>
>> Vassiliev V. V. wrote:
>>> Connect to Access database and execute:
>>>
>>> INSERT INTO MyTable(Col1, Col2)
>>> SELECT Col1, Col2
>>> FROM [Sheet1$] IN ""[Excel 8.0;Database=c:\myxls.xls]
>>>
>>> [Sheet1] - named diapason, [Sheet1$] - sheet (if it does not work, try 
>>> [$Sheet1]), [Sheet1$B5:C10] - cells range.
>>>
>>> //------------------------------------------
>>> Regards,
>>> Vassiliev V. V.
>>> http://www.managed-vcl.com - using .Net objects in Delphi for Win32 +
>>> ADO.Net
>>> http://www.oledbdirect.com - The fastest way to access MS SQL Server,
>>> MS Jet (Access) and Interbase (through OLEDB)
>>>
>>>
>>> "Tomislav Hundric'" <rifnet@gmail.com> �������/�������� � �������� 
>>> ���������: news:46dae1d4$1@newsgroups.borland.com...
>>>> Please help,
>>>> I need to populate database table from excel file. My database table has 
>>>> same fields as excel file has columns. I use ADO connection to connect 
>>>> to database. Can anyone give me some guide lines or example how to do 
>>>> this.
>>>>
>>>> Thanks in advance.
> 

Try to open query (with ADOQuery1->Active = true):

SELECT Column1, Column2
FROM [Sheet1$] IN ""[Excel 8.0;Database=c:\testing.xls]

There should be no parameters in query - Column1, Column2 are column names, 
not parameters. If it fails, try:

SELECT *
FROM [Sheet1$] IN ""[Excel 8.0;Database=c:\testing.xls]

it will show you column names how Jet driver sees them. Then adjust insert 
query should be easy

//------------------------------------------
Regards,
Vassiliev V. V.
http://www.managed-vcl.com - using .Net objects in Delphi for Win32 +
ADO.Net
http://www.oledbdirect.com - The fastest way to access MS SQL Server,
MS Jet (Access) and Interbase (through OLEDB)


"Tomislav Hundric'" <rifnet@gmail.com> �������/�������� � �������� 
���������: news:46dc4fec@newsgroups.borland.com...
> Thanks again on your help, I faced problems once again, I'm sorry but the 
> following is giving me a headache:
>
> Access database:
>     ID (AutoNumber)
>     Field1 (Text)
>     Field2 (Text)
>
> ADOQuery1
>     ParamCheck=false
>     Parameters
>        Column1
>        Column2
>     SQL
>        INSERT INTO Test (Field1, Field2)
>        SELECT Column1, Column2
>        FROM [Sheet1$] IN ""[Excel 8.0;Database=c:\testing.xls]
>
> Excel file:
>     Column1 Column2
>     1.Test11 Test21
>     2.Test12 Test22
>     3.Test13 Test23
>
> Form
> void __fastcall TForm1::Button1Click(TObject *Sender)
> {
> ADOQuery1->ExecSQL();
> }
>
> So everything goes well, but when I open database file in access, there 
> are two empty rows created. I don't know why there are two (not three) 
> rows created and why are they empty.
>
> Thanks in advance,
> Tomislav.
>
>
>
> Vassiliev V. V. wrote:
>> Set ParamCheck to false. Otherwise it treats :anything as parameter, in 
>> this case c:\myxls.xls. Use also ExecSQL - this query does not return any 
>> data.
>>
>> //------------------------------------------
>> Regards,
>> Vassiliev V. V.
>> http://www.managed-vcl.com - using .Net objects in Delphi for Win32 +
>> ADO.Net
>> http://www.oledbdirect.com - The fastest way to access MS SQL Server,
>> MS Jet (Access) and Interbase (through OLEDB)
>>
>>
>> "Tomislav Hundric'" <rifnet@gmail.com> �������/�������� � �������� 
>> ���������: news:46dc2b02@newsgroups.borland.com...
>>> First of all thank you on your answer and sorry for my late response,
>>> I'm using ADOConnection and ADOQuery, so I've tried to enter code you've
>>> posted to SQL of ADOQuery, but I can't seem to make connection to excel 
>>> file.
>>>
>>> using your code:
>>>
>>>> INSERT INTO MyTable(Col1, Col2)
>>>> SELECT Col1, Col2
>>>> FROM [Sheet1$] IN ""[Excel 8.0;Database=c:\myxls.xls]
>>> and changing ADOQuery1->Active = false to ADOQuery1->Active = true
>>>
>>> I get error: "Parameter object is improperly defined. Inconsistent or 
>>> incomplete information was provided."
>>>
>>> First I thought quotation marks were misplaced, but I've tried every 
>>> possible position without success.
>>>
>>>
>>> I get the syntax, but don't know what to define in from clause, do I 
>>> need to add something else like :
>>> FROM [Sheet1$] IN ['Microsoft.Jet.OLEDB.4.0',
>>> 'Excel 8.0;Database=C:\testing.xls;']
>>>
>>> Thanks in advance,
>>> Tomislav.
>>>
>>>
>>> Vassiliev V. V. wrote:
>>>> Connect to Access database and execute:
>>>>
>>>> INSERT INTO MyTable(Col1, Col2)
>>>> SELECT Col1, Col2
>>>> FROM [Sheet1$] IN ""[Excel 8.0;Database=c:\myxls.xls]
>>>>
>>>> [Sheet1] - named diapason, [Sheet1$] - sheet (if it does not work, try 
>>>> [$Sheet1]), [Sheet1$B5:C10] - cells range.
>>>>
>>>> //------------------------------------------
>>>> Regards,
>>>> Vassiliev V. V.
>>>> http://www.managed-vcl.com - using .Net objects in Delphi for Win32 +
>>>> ADO.Net
>>>> http://www.oledbdirect.com - The fastest way to access MS SQL Server,
>>>> MS Jet (Access) and Interbase (through OLEDB)
>>>>
>>>>
>>>> "Tomislav Hundric'" <rifnet@gmail.com> �������/�������� � �������� 
>>>> ���������: news:46dae1d4$1@newsgroups.borland.com...
>>>>> Please help,
>>>>> I need to populate database table from excel file. My database table 
>>>>> has same fields as excel file has columns. I use ADO connection to 
>>>>> connect to database. Can anyone give me some guide lines or example 
>>>>> how to do this.
>>>>>
>>>>> Thanks in advance.
>> 

Hi Vassiliev,
again you helped me with what I needed, the problem was that the names 
of the columns were the first rows, so they were Test1 and Test2, I 
simply changed the first row values to Column 1 and Column 2 and now 
everything works as expected.

Big thanks, I'll maby have some other questions for you regarding this 
subject, since I'll be developing something around this. It's really 
nice to know that there's someone willing to help and share knowledge 
with others.

Thanks,
Tomislav.

Vassiliev V. V. wrote:
> Try to open query (with ADOQuery1->Active = true):
> 
> SELECT Column1, Column2
> FROM [Sheet1$] IN ""[Excel 8.0;Database=c:\testing.xls]
> 
> There should be no parameters in query - Column1, Column2 are column names, 
> not parameters. If it fails, try:
> 
> SELECT *
> FROM [Sheet1$] IN ""[Excel 8.0;Database=c:\testing.xls]
> 
> it will show you column names how Jet driver sees them. Then adjust insert 
> query should be easy
> 
> //------------------------------------------
> Regards,
> Vassiliev V. V.
> http://www.managed-vcl.com - using .Net objects in Delphi for Win32 +
> ADO.Net
> http://www.oledbdirect.com - The fastest way to access MS SQL Server,
> MS Jet (Access) and Interbase (through OLEDB)
> 
> 
> "Tomislav Hundric'" <rifnet@gmail.com> �������/�������� � �������� 
> ���������: news:46dc4fec@newsgroups.borland.com...
>> Thanks again on your help, I faced problems once again, I'm sorry but the 
>> following is giving me a headache:
>>
>> Access database:
>>     ID (AutoNumber)
>>     Field1 (Text)
>>     Field2 (Text)
>>
>> ADOQuery1
>>     ParamCheck=false
>>     Parameters
>>        Column1
>>        Column2
>>     SQL
>>        INSERT INTO Test (Field1, Field2)
>>        SELECT Column1, Column2
>>        FROM [Sheet1$] IN ""[Excel 8.0;Database=c:\testing.xls]
>>
>> Excel file:
>>     Column1 Column2
>>     1.Test11 Test21
>>     2.Test12 Test22
>>     3.Test13 Test23
>>
>> Form
>> void __fastcall TForm1::Button1Click(TObject *Sender)
>> {
>> ADOQuery1->ExecSQL();
>> }
>>
>> So everything goes well, but when I open database file in access, there 
>> are two empty rows created. I don't know why there are two (not three) 
>> rows created and why are they empty.
>>
>> Thanks in advance,
>> Tomislav.
>>
>>
>>
>> Vassiliev V. V. wrote:
>>> Set ParamCheck to false. Otherwise it treats :anything as parameter, in 
>>> this case c:\myxls.xls. Use also ExecSQL - this query does not return any 
>>> data.
>>>
>>> //------------------------------------------
>>> Regards,
>>> Vassiliev V. V.
>>> http://www.managed-vcl.com - using .Net objects in Delphi for Win32 +
>>> ADO.Net
>>> http://www.oledbdirect.com - The fastest way to access MS SQL Server,
>>> MS Jet (Access) and Interbase (through OLEDB)
>>>
>>>
>>> "Tomislav Hundric'" <rifnet@gmail.com> �������/�������� � �������� 
>>> ���������: news:46dc2b02@newsgroups.borland.com...
>>>> First of all thank you on your answer and sorry for my late response,
>>>> I'm using ADOConnection and ADOQuery, so I've tried to enter code you've
>>>> posted to SQL of ADOQuery, but I can't seem to make connection to excel 
>>>> file.
>>>>
>>>> using your code:
>>>>
>>>>> INSERT INTO MyTable(Col1, Col2)
>>>>> SELECT Col1, Col2
>>>>> FROM [Sheet1$] IN ""[Excel 8.0;Database=c:\myxls.xls]
>>>> and changing ADOQuery1->Active = false to ADOQuery1->Active = true
>>>>
>>>> I get error: "Parameter object is improperly defined. Inconsistent or 
>>>> incomplete information was provided."
>>>>
>>>> First I thought quotation marks were misplaced, but I've tried every 
>>>> possible position without success.
>>>>
>>>>
>>>> I get the syntax, but don't know what to define in from clause, do I 
>>>> need to add something else like :
>>>> FROM [Sheet1$] IN ['Microsoft.Jet.OLEDB.4.0',
>>>> 'Excel 8.0;Database=C:\testing.xls;']
>>>>
>>>> Thanks in advance,
>>>> Tomislav.
>>>>
>>>>
>>>> Vassiliev V. V. wrote:
>>>>> Connect to Access database and execute:
>>>>>
>>>>> INSERT INTO MyTable(Col1, Col2)
>>>>> SELECT Col1, Col2
>>>>> FROM [Sheet1$] IN ""[Excel 8.0;Database=c:\myxls.xls]
>>>>>
>>>>> [Sheet1] - named diapason, [Sheet1$] - sheet (if it does not work, try 
>>>>> [$Sheet1]), [Sheet1$B5:C10] - cells range.
>>>>>
>>>>> //------------------------------------------
>>>>> Regards,
>>>>> Vassiliev V. V.
>>>>> http://www.managed-vcl.com - using .Net objects in Delphi for Win32 +
>>>>> ADO.Net
>>>>> http://www.oledbdirect.com - The fastest way to access MS SQL Server,
>>>>> MS Jet (Access) and Interbase (through OLEDB)
>>>>>
>>>>>
>>>>> "Tomislav Hundric'" <rifnet@gmail.com> �������/�������� � �������� 
>>>>> ���������: news:46dae1d4$1@newsgroups.borland.com...
>>>>>> Please help,
>>>>>> I need to populate database table from excel file. My database table 
>>>>>> has same fields as excel file has columns. I use ADO connection to 
>>>>>> connect to database. Can anyone give me some guide lines or example 
>>>>>> how to do this.
>>>>>>
>>>>>> Thanks in advance.
> 
>