Populate a SQL table from a array

Hello i am extracting some range from a excel sheet. i am working in VB
worksheet.Range("clientes").Cells.Value(excel.XlRangeValueDataType.xlRangeValueDefault)
It return me a array objet How can i insert this array into a SQl table
Thanks
Saul_lAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

blandyukCommented:
Simply execute an INSERT statement and bind the array data values to it:

INSERT INTO [table_name] (field1, field2, field3, etc)
VALUES (" + array(0) + ", " + array(1) + ", " + array(2) + ...)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Saul_lAuthor Commented:
thanks, but the problem is that the array is multidimensional (1,1) (1,2) (2,1).........
and the other problem is that i dont know how many columns i have.
thanks
0
Miguel OzSoftware EngineerCommented:
Result arrays are one based. (object[,])
Dim Values(,) As Object = worksheet.Range("clientes").Cells.Value(excel.XlRangeValueDataType.xlRangeValueDefault)

To get the number of columns
Dim colMax As Integer = Values.GetUpperBound(1)

Then you can rewrite the insert statement as:
INSERT INTO [table_name] (field1, field2, field3, etc)
VALUES (" + Values(1,1) + ", " + Values(1,2) + ", " + Values(1,3) + ... +Values(1,colMax))

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Saul_lAuthor Commented:
thanks a lot.
I am tring also to get the range values as XML this is my instruction
Dim valores = Worksheet.Range("clientes[#todo]").Value(excel.XlRangeValueDataType.xlRangeValueXMLSpreadsheet)

and then this XML i am trying to insert it in SQL table using this code
EXEC sp_xml_preparedocument @XMLDocPointer OUTPUT, @strXML
 INSERT INTO Clientes SELECT * FROM OPENXML(@XMLDocPointer,'/Table',2)

i get an error that the definition not correspond to the table any idea?

thanks
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
  <Style ss:ID="s62">
   <Borders/>
  </Style>
 </Styles>
 <Worksheet ss:Name="Hoja1">
  <Names>
   <NamedRange ss:Name="DatosExternos_1" ss:RefersTo="=Hoja1!R1C1:R15C13"/>
  </Names>
  <Table ss:ExpandedColumnCount="13" ss:ExpandedRowCount="15"
   ss:DefaultColumnWidth="60" ss:DefaultRowHeight="15">
   <Column ss:Width="52.5"/>
   <Column ss:Width="45"/>
   <Column ss:Width="51"/>
   <Column ss:Width="54.75"/>
   <Column ss:Width="48.75"/>
   <Column ss:Width="52.5" ss:Span="1"/>
   <Column ss:Index="8" ss:Width="38.25"/>
   <Column ss:Width="36.75"/>
   <Column ss:Width="48"/>
   <Column ss:Width="32.25"/>
   <Column ss:Width="27.75"/>
   <Row>
    <Cell><Data ss:Type="String">NoCliente</Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Nombre</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">ApellidoP</Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">ApellidoM</Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Direccion</Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Telefono1</Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Telefono2</Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Celular</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Correo</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Cobrador</Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Titulo</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Zona</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Comentario</Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
   </Row>
   <Row>
    <Cell ss:StyleID="s62"><Data ss:Type="Number">1</Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String">Saul</Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String">Laniado</Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String">Roman</Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String"></Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String"></Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String"></Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String"></Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String">nuevo</Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String"></Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String">Sr.</Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String"></Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
    <Cell ss:StyleID="s62"><Data ss:Type="String"></Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">2</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">saul</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">romano</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">nuevo</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Sr.</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">3</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Nissim</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Memun</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">sa@</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Sr.</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">4</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Shlomi</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">abadie</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String"></Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">6</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">salo</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">laniado</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">7</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">salomon</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">laniado</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">8</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">saul1</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">laniado1</Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
    <Cell ss:Index="9"><Data ss:Type="String">sa@</Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">9</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">saul2</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">laniado2</Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">10</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">david</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Laniado</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Romano</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">11</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">David</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Cihen</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Cherem</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">12</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Jonny</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">perez</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">preer</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">13</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Moises</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Cohen</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">alfie</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">14</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Dan</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Cohen</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">Levy</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">15</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">saul8</Data><NamedCell ss:Name="DatosExternos_1"/></Cell>
    <Cell><Data ss:Type="String">laniado2</Data><NamedCell
      ss:Name="DatosExternos_1"/></Cell>
   </Row>
  </Table>
 </Worksheet>
</Workbook>

Open in new window

0
Miguel OzSoftware EngineerCommented:
Glad it helps to solve your original question,
Regarding your next question please post your complete error message.
What is "Clientes" sql definition?
0
Saul_lAuthor Commented:
I think yhe firs Question was solved and the second Question posted a other question to not mix the questions
thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.