Link to home
Start Free TrialLog in
Avatar of Saul_l
Saul_l

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of blandyuk
blandyuk
Flag of United Kingdom of Great Britain and Northern Ireland image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Saul_l
Saul_l

ASKER

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
SOLUTION
Avatar of Miguel Oz
Miguel Oz
Flag of Australia image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Saul_l
Saul_l

ASKER

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

Avatar of Miguel Oz
Miguel Oz
Flag of Australia image
Glad it helps to solve your original question,
Regarding your next question please post your complete error message.
What is "Clientes" sql definition?
Avatar of Saul_l
Saul_l

ASKER

I think yhe firs Question was solved and the second Question posted a other question to not mix the questions
thanks