В 12 статье рассмотрели работу в EXCEL по обмену данными между файлами и вывод данных из EXCEL файлов в текстовый файл. Да текстовый файл создавался с разделителями. Можно по-другому перевести в текстовый файл. Я об этом уже писал.
Можно применить v=RSQ.GetString с последующим выводом в текстовый файл.
Прочитать блокнотом или другими приложениями. В этой статье я расскажу, как вывести данные из текстового файла в файлы EXCEL. Прежде чем это сделать, Я поясню, что этот вывод немного похож на вывод данных из текстовых файлов в dbf файл. Данные необходимо вывести автоматом, подобрав длину строки в символах с понятием символов разделителей. Если файл не имеет четких символов разделителей, то это приведет к ошибке. Можно не делать нормализацию из текстового файла по полям-заголовкам, а можно и делать. EXCEL файл это не файл dbf, где нужна нормализация и вполне подойдет сделать autofit в явном решении. Можно потом сделать нормализацию из EXCEL файла и сортировку по критериям, с которыми можно определиться. Не из всех текстовых файлов легко перевести данные. У меня бывали случаи, когда Я переводил в EXCEL большие по размеру и объему текстовые файлы, а бывали когда разбирался и отрабатывал незначительные. И, еще пресловутый АПОСТРОФ, который ставится перед данными и его нельзя убрать с помощью ADO. Я не нашел такого решения и возможно в ADO его нет. Его можно убрать только явно алгоритмом или скриптом или в самом приложении EXCEL. Но он и не мешает, чтобы сильно беспокоится. В основном я переношу данные в текстовом формате.
Я подготовил 3 файла работы с текстовым файлом _111.txt. Это файлы AdoED13A.vbs, AdoED13B.vbs, AdoED13C.vbs. Файл AdoED13B.vbs я опишу подробнее. Тексты других файлов в некотором случае совпадают с текстом этого файла. В нем больше информации для размышления. Предоставляю файл AdoED13B.vbs
По ходу приложу некоторые комментарии.
'*******************************************************************
' Имя: AdoED13B.vbs
' Язык: VBScript
' Описание: Вывод данных из TXT файла с разделителями в EXCEL
' ODBC и ADO только для EXCEL
' С подключение строки с файлом из консоли
' Нормализация из текстового файла
' Операции с данными txt и EXCEL
'*******************************************************************
Set WshShell=WScript.CreateObject("WScript.Shell")
SDefaultDir=WshShell.CurrentDirectory
Set FSO = CreateObject("Scripting.FileSystemObject")
' ==== TXT файл который должен уже находится в текущей директории ====
fileTXT=SDefaultDir & "\test1.txt"
'========== Проверка файла
If FSO.FileExists(FileTXT) Then
else
MsgBox "Нет файла "&FileTXT
WScript.Quit
end if
'========== Проверка файла
Set FileName = FSO.GetFile(fileTXT)
Set TextStream = FileName.OpenAsTextStream(1)
' ==== Создаваемый EXCEL файл ====
NameFil=SDefaultDir & "\_222.xls"
'=========== Проверка файла xls ===
If FSO.FileExists(NameFil) Then
FSO.DeleteFile NameFil
End if
'=========== Проверка файла dbf ===
'======================= EXCEL =============
Set cnnMain = CreateObject("ADODB.Connection")
Set cnnMainR = CreateObject("ADODB.Recordset")
cnnMain.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=" & NameFil & ";Deleted=0;ReadOnly=0;Exclusive=0;"
cnnMain.CursorLocation = 3
'======================= EXCEL =============
'========== Количество столбцов =======
Kstl=0
Str = Trim(TextStream.ReadLine())
for i=1 to Len(str)
if Mid(str,i,1)="|" Then
Kstl=Kstl+1
end if
next
if Kstl=0 then
WScript.Echo("Неправильно указан символ разделитель или нет такого символа")
WScript.Quit
end if
Set TextStream = FileName.OpenAsTextStream(1)
ReDim MassStrS(Kstl),MassMax(Kstl)
iss=1
nn=0
While Not TextStream.AtEndOfStream
Str = TextStream.ReadLine()
nn=nn+1
for j=1 to Kstl
InSimv=InStr(iss,Str,"|",1) ' Ставим символ разделитель строки
st=Trim(Mid(Str,iss,InSimv-iss))
MassStrS(j)=len(st)
if MassStrS(j)=0 then
MassStrS(j)=9
end if
if nn=1 then
MassMax(j)=MassStrS(j)
else
if MassMax(j)<MassStrS(j) then
MassMax(j)=MassStrS(j)
end if
end if
iss=InSimv+1
next
iss=1
Wend
nn=0
Erase MassStrS
SumSimv=0
for i=1 to Kstl
SumSimv=SumSimv+MassMax(i)
next
if SumSimv>=3810 then
WScript.Echo("Общее количество символов больше 3810")
WScript.Quit
end if
' ==============Ввод названий Полей базы данных Можно и так
'SQL="create table " & NameFil & ".[xls] ("
'for i=1 to Kstl
' if i=Kstl then
' SQL=SQL & "st" & i & " TEXT)"
' else
' SQL=SQL & "st" & i & " TEXT,"
' end if
'next
' ==============Ввод названий Полей базы данных
'==============Ввод названий Полей базы данных для EXCEL
SQL="create table " & Namefil & ".[xls] ("
for i=1 to Kstl
stDL= MassMax(i) ' Здесь можно подобрать длину поля (имя)
if i=Kstl then
SQL=SQL & "st" & i & " char(" & stDL & "))"
else
SQL=SQL & "st" & i & " char(" & stDL & "),"
end if
next
Erase MassMax
'==============Ввод названий Полей базы данных для EXCEL
cnnMainR.open SQL,cnnMain,3 ' Создание шапки с заголовками в EXCEL
'==============Вывод данных из текстового файла в EXCEL
Set TextStream = FileName.OpenAsTextStream(1)
iss=1
While Not TextStream.AtEndOfStream
Str = TextStream.ReadLine()
vvv="'"
stN="("
for j=1 to Kstl
InSimv=InStr(iss,Str,"|",1) ' Ставим символ разделитель строки
PoStr=Mid(Str,iss,InSimv-iss)
PoStr=DosToWin(PoStr)
st=Trim(PoStr)
stName="st"&j ' должно совпадать с "st" & i
if j=Kstl then
stN=stN & stName & ")"
vvv=vvv & st & "'"
'vvv=vvv & st & " "
else
stN=stN & stName & ","
vvv=vvv & st & "'" & "," & "'"
end if
iss=InSimv+1
next
iss=1
' SQL="insert into " & NameFil & ".[xls] " & stN & " values(" & vvv & ")"
SQL="insert into " & NameFil & ".[xls] values(" & vvv & ")"
cnnMainR.open SQL,cnnMain,3
Wend
'==============Вывод данных из текстового файла в EXCEL
'Вот здесь снова запускается EXCEL файл и можно показать количество фильтров и сортировки.
cnnMain.close
Set cnnMain = CreateObject("ADODB.Connection")
Set cnnMainR = CreateObject("ADODB.Recordset")
cnnMain.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=" & NameFil & ";Deleted=0;ReadOnly=0;Exclusive=0;"
cnnMain.CursorLocation = 3
strSQLn="SELECT * FROM " & NameFil & ".[xls$]"
cnnMainR.open strSQLn,cnnMain,2,2
Set RSK = cnnMain.Execute(strSQLn)
KlFieldsN = RSK.Fields.count
KlRecsN=RSK.RecordCount
stNameN=""
for j=0 to KlFieldsN-1
stName=RSK.Fields(j).name
if j=KlFieldsN-1 then
stNameN=stNameN & stName
else
stNameN= stNameN & stName & ","
end if
next
' strSQLn="SELECT * FROM [xls$] where IsNull(st2)=false ORDER BY " & stNameN & " " 'Правильно автомат по всему листу
' strSQLn="SELECT st2 FROM [xls$] IsNull(st2)=false ORDER BY st2" 'Можно и так
' strSQLn="SELECT* FROM [xls$]" 'Просто выбор данных с листа xls и без NULL
' strSQLn="SELECT * FROM [xls$A12:D5] where IsNull(st2)=false ORDER BY st2" 'Можно и так
' strSQLn="SELECT * FROM [xls$C5:B1] where IsNull(st2)=false ORDER BY st2" 'Можно и так
strSQLn="SELECT * FROM [xls$] where IsNull(st2)=false ORDER BY st2" 'Можно и так
Set RSK = cnnMain.Execute(strSQLn)
KlFieldsN = RSK.Fields.count
KlRecsN=RSK.RecordCount
strSQlDelNlist="DROP TABLE " & NameFil & ".[xls$A2:D]"
cnnMain.Execute(strSQlDelNlist)
'********************************** 1 фрагмент *********************
'После фильтров и сортировки возвращаем из RecordSet данные и снова записываем в EXCEL файл. Можно сказать, данные из текстового файла можно записать в любой сортировке и фильтра. Конечно, можно отобрать определенные данные, когда переносим их из текстового файла по созданным для этого условий с помощью фильтров и условий в комбинации if…then. Таким образом, данные текстового файла можно регулировать и продумывать сценарии их записи и обработки.
On Error Resume Next 'Проверка ошибок
RSK.MoveFirst
cnnMainR.MoveFirst
cnnMainR.Requery
for i=1 to KlRecsN
for j=0 to KlFieldsN-1
st=RSK.Fields(j)
stName=RSK.Fields(j).name
cnnMainR.Fields(stName).Value=st
'cnnMainR.Fields(j).Value=st 'Можно и так
next
KlRecsNew=i+1
strSQlDelNlist="DROP TABLE [xls$A" & KlRecsNew & ":D" & KlRecsNew & "]"
cnnMain.Execute(strSQlDelNlist)
cnnMainR.MoveNext
RSK.MoveNext
next
strSQlDelNlist="DROP TABLE [xls$A" & KlRecsN+2 & ":D]"
cnnMain.Execute(strSQlDelNlist)
On Error goto 0
'********************************** 1 фрагмент *********************
'WScript.Echo("Фрагмент 1. Файл создан!")
'WScript.Quit' Если хотите просмотреть предыдущие решения
'********************************** 2 фрагмент *********************
'В этом фрагменте после фильтров и сортировки с данными уже в EXCEL можно проводить различные операции. Здесь мы добавляем к данным 2 поля счетчик массива.
Если хотите посмотреть, что произошло, то уберите знак ' (апостроф) с этих строк.
'WScript.Echo("Фрагмент 2. Файл создан!")
'WScript.Quit' Если хотите просмотреть предыдущие решения
Лучше посмотреть что получилось.
cnnMain.close
Set cnnMain = CreateObject("ADODB.Connection")
cnnMain.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=" & NameFil & ";Deleted=0;ReadOnly=0;Exclusive=0;"
cnnMain.CursorLocation = 3
strSQLn="SELECT * FROM " & NameFil & ".[xls$]"
cnnMainR.open strSQLn,cnnMain,2,2
Set RSK = cnnMain.Execute(strSQLn)
KlFieldsN = RSK.Fields.count
KlRecsN=RSK.RecordCount
On Error Resume Next 'Проверка ошибок
cnnMainR.MoveFirst
cnnMainR.Requery
for i=1 to KlRecsN
stDn1=trim(cnnMainR.Fields(1).value)
cnnMainR.Fields("st2").Value=stDn1 & i
'cnnMainR.Fields(1).Value=stDn1 & i 'Можно и так
cnnMainR.Update
KlRecsNew=i+1
strSQlDelNlist="DROP TABLE [Sheet1$A" & KlRecsNew & ":D" & KlRecsNew & "]"
cnnMain.Execute(strSQlDelNlist)
cnnMainR.MoveNext
next
On Error goto 0
'********************************** 2 фрагмент *********************
'WScript.Echo("Фрагмент 2. Файл создан!")
'WScript.Quit' Если хотите просмотреть предыдущие решения
'********************************** 3 фрагмент *********************
'А вот этот фрагмент самый интересный. Здесь можно отобразить данные выбранные фильтром по ячейкам, если будет необходимо. Можно отсортировать и записать в разные колонки. Здесь приводится выбор по ячейкам и сортировка.
'strSQLn="SELECT * FROM [xls$A4:D9]" 'Просто выбор данных с листа xls
strSQLn="SELECT * FROM [xls$A4:D9] ORDER BY 1" ' Сортировка по полю st1
Set RSK = cnnMain.Execute(strSQLn)
KlRecsN=RSK.RecordCount
strSQlDelNlist="DROP TABLE " & NameFil & ".[xls$A2:D]"
cnnMain.Execute(strSQlDelNlist)
RSK.MoveFirst
cnnMainR.MoveFirst
cnnMainR.Requery
On Error Resume Next 'Проверка ошибок
for i=1 to KlRecsN
for j=0 to KlFieldsN-1
st=RSK.Fields(j)
cnnMainR.Fields(j).Value=st
next
KlRecsNew=i+1
strSQlDelNlist="DROP TABLE [xls$A" & KlRecsNew & ":D" & KlRecsNew & "]"
cnnMain.Execute(strSQlDelNlist)
cnnMainR.MoveNext
RSK.MoveNext
next
strSQlDelNlist="DROP TABLE [xls$A" & KlRecsN+1 & ":D]"
cnnMain.Execute(strSQlDelNlist)
On Error goto 0
'********************************** 3 фрагмент *********************
'------------------------ Функция преобразования символов в русскую кодировку ----------
Function DosToWin(s)
ss=""
For i=1 To Len(s) ' Проверяем все символы в строке.
k = Asc(Mid(s,i,1)) ' Рассматриваем и определяем ANSI-код каждого символа. Шаг 1 символ в строке
If (k>=128) And (k <= 175) Then
k=k+64
ElseIf (k>=224) And (k <= 239) Then
k=k+16
ElseIf k = 240 Then ' Символ Ё
k=168
ElseIf k = 241 Then ' Символ ё
k=184
ElseIf k = 252 Then ' Символ №
k=185
End If
ss=ss+Chr(k) ' Возвращаем преобразованную строку
Next
DosToWin=ss
End Function
'------------------------ Функция ----------
Set WshShell=Nothing
Set FSO = Nothing
Set FileName=Nothing
TextStream.Close
Set TextStream=Nothing
cnnMain.Close
Set cnnMain = Nothing
Set cnnMainR = Nothing
WScript.Echo("Файл создан!")
WScript.Quit
'*******************************************************************
Остальное как обычно.
Можно отредактировать и снова в dbf или в текстовый файл.
Как я в предыдущей статье указал - круговорот данных в масштабных понятиях работы с данными.
Перед запуском скриптов файл _111.txt сохраните в другом каталоге для повторного применения, если случайно испортите или удалите.
Процесс переноса данных в EXCEL из больших текстовых файлов также может быть долгим и зависит от размера и объема текстового файла и мощности компьютера.
Все файлы сохранил в архив Файлы13.rar
Пробуйте.
Ссылка на файл https://disk.yandex.ru/d/XBtRb3ku8V2z0Q
Подписывайтесь на мой канал и ставьте лайки.
