如何导入多个大容量文本文件的每行最后10位字符串？

zhaogang1960

thumb2004 发表于 2014-10-21 21:32
Dim cnn As Object, SQL$
      Set cnn = CreateObject("ADODB.Connection")
     cnn.Open "Provid ...

1. Dim cnn As Object, SQL$
   
2. Set cnn = CreateObject("ADODB.Connection")
   
3. cnn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Extended Properties='text;hdr=no';Data Source=" & ThisWorkbook.Path
   
4. SQL = "select mid(f1,225,21) ,right(f1,1) from demo.txt"
   
5. [b2].CopyFromRecordset cnn.Execute(SQL)
   
6. cnn.Close
   
7. Set cnn = Nothing

复制代码

thumb2004

解决。
Sub 按钮1_Click()
Dim cnn As Object, SQL$
      Set cnn = CreateObject("ADODB.Connection")
     cnn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Extended Properties='text;hdr=no;FMT=FixedLength;CharacterSet=OEM';Data Source=" & ThisWorkbook.Path
     SQL = "select mid(f1,224,16) ,right(f1,1) from demo.txt"
    ' SQL = "select *  from demo.txt"
     [b2].CopyFromRecordset cnn.Execute(SQL)
     cnn.Close
     Set cnn = Nothing

End Sub

但是如何在sql语句中，将返回的字符串中选择下划线后面的一部分呢？如 ”李先生_1234567890" 变成“1234567890”？

zhaogang1960

thumb2004 发表于 2014-10-21 22:37
解决。
Sub 按钮1_Click()
Dim cnn As Object, SQL$

instr函数：

1. Dim cnn As Object, SQL$
   
2. Set cnn = CreateObject("ADODB.Connection")
   
3. cnn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Extended Properties='text;hdr=no';Data Source=" & ThisWorkbook.Path
   
4. SQL = "select mid(f1,instr(f1,'_')+1,16) ,right(f1,1) from demo.txt"
   
5. [b2].CopyFromRecordset cnn.Execute(SQL)
   
6. cnn.Close
   
7. Set cnn = Nothing
   

复制代码

thumb2004

Dim cnn As Object, SQL$
      Set cnn = CreateObject("ADODB.Connection")
     cnn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Extended Properties='text;hdr=no;FMT=FixedLength;CharacterSet=OEM';Data Source=" & ThisWorkbook.Path
     SQL = "select trim(mid(mid(f1,224,16),instr(mid(f1,224,16),'_')+1,16)) ,right(f1,1) from demo.txt"
    ' SQL = "select *  from demo.txt"
     [b2].CopyFromRecordset cnn.Execute(SQL)
     cnn.Close
     Set cnn = Nothing

End Sub

这样可以了