Страницы: 1
RSS
Распределить данные из файла txt по столбцам EXCEL
 
Доброго дня, помогите пожалуйста, должно быть какое-то стандартное решение этой задачи, но я не могу найти,
прошу Вашей помощи!
Есть текстовый файл в котором данные представлены вот в таком формате:
[1,3,10,27,31,36],[1,3,10,27,31,39],[1,3,10,27,32,35],[1,3,10,27,32,38],[1,3,10,27,33,37]
файл очень объемный, ручками если делать не вариант, это на всю жизнь...
эти данные надо распределить по шести столбикам, т.е. числа с скобках разделённые запятой распределяем в шесть столбиков.
Помогите пожалуйста.  
 
Вкладка "Данные"-"Получение данных" - "Из текста"
Далее мастер подскажет.
 
я уже пробовал так, не получается... возможно в формулировках вопросов не разберусь...
или кодировку надо какую-то указывать особенную...
 
Добрый день!
Файл приложите, пжл.
И у Вас в строке 5 потенциальных столбцов, а не 6.
 
почему 5?, вот строка:
[1,3,10,27,31,36],[1,3,10,27,31,39],[1,3,10,27,32,35],[1,3,10,27,32,38],[1,3,10,27,33,37]
количество чисел - 6, каждое число в своём столбце (в ячейке своего столбца),
файл прилагаю.
Изменено: Katsu - 28.12.2019 15:50:43 (ошибка)
 
Для лучшего понимания вот такой должен получится Excel файл
 
Katsu, в Power Query можно сделать
Код
let
    Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:\abc\Данные.txt"), null, null, 1251)}),
    #"Extracted Text After Delimiter" = Table.TransformColumns(Source, {{"Column1", each Text.AfterDelimiter(_, "["), type text}}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Extracted Text After Delimiter", {{"Column1", each Text.BeforeDelimiter(_, "]", {0, RelativePosition.FromEnd}), type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Extracted Text Before Delimiter", {{"Column1", Splitter.SplitTextByDelimiter("],[", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.1", Int64.Type}, {"Column1.2", Int64.Type}, {"Column1.3", Int64.Type}, {"Column1.4", Int64.Type}, {"Column1.5", Int64.Type}, {"Column1.6", Int64.Type}})
in
    #"Changed Type1"
 
Есть решение в три шага.
1. Импортируете текст. Разделитель ]
2. В первой строке Найти и заменить ,[ на пусто.
3.В код листа вставляете макрос и запускаете его.

Разумеется, все это можно одним макросом сделать.
Код
Sub CelSplit()
Dim Record
j = 2
For i = 1 To [A1].CurrentRegion.Columns.Count
    Record = Split(Cells(1, i).Value, ",")
    Cells(j, 1).Resize(, 6) = Record
    j = j + 1
Next
End Sub
Изменено: Smurov - 28.12.2019 16:26:34
 
Smurov, вторым шагом, наверное, надо транспонировать, затем заменить, затем разделить по запятой. и все без макроса
Изменено: artyrH - 28.12.2019 16:36:56
 
artyrH, Да Вы правы, спасибо, просто я люблю макросы)
 
Доброе время суток.
Ещё вариант на Power Query
Код
let
    Source = "[" & Lines.FromBinary(File.Contents("C:\Path\Данные.txt"), null, null, 1251){0} & "]",
    result = Table.FromRows(Json.Document(Source))
in
    result
 
Андрей VG, круто! Как и всегда
не подскажете где поподробнее почитать о функции  Json.Document ?
 
Цитата
artyrH написал:
где поподробнее почитать о функции  Json.Document
Ну, в общем случае Json.Document, но вам скорее нужно почитать про структуру Json
 
Андрей VG, спасибо
 
Всем большое спасибо)
однако не знаю куда эти коды прикрутить... и как?
Если Вас  не затруднит, объясните пожалуйста...
Изменено: Katsu - 28.12.2019 23:20:13 (ошибка)
 
Katsu, у вас же один текстовый файл. попробуйте сделать так:
открыть в Excel файл txt, указав разделитель   ]
затем выделить всю строку, нажав единицу в нумерации строк
Ctrl+C и выделив ячейку A3 -> Ctrl+V, выбрав Транспонировать
Далее, не снимая выделения -> Ctrl+H
найти  ,[ и заменить на пусто, после найти [ и заменить на пусто
Далее, не снимая выделения идем в Данные->Текст по столбцам
выбираем разделитель - запятая .  все

а как вставить код в макрос или как вставить запрос в Power Query можно нагуглить
 
спасибо, потыкался по Вашей рекомендации, все работает, но данных оказалось много и они на поместились в строку, т.е. срезались...
сейчас пришлю содержание сообщения от EXCEL.
 
Excel сообщает, что :

Текстовый файл содержит больше данных, чем может поместиться на листе.
Чтобы выполнить импорт только тех данных, которые могут поместиться нажмите ОК.
Затем можно импортировать остальные данные, повторяя операцию на другом листе..и т.д.

Причем на листе заполняется только первая строка,
а как задействовать дополнительные строки, что можно было импортировать всё за один раз, я не придумал..., может подскажите?
 
Katsu, специально для вас, вывод со второй строки:
Код
Sub Read_TXT()
    Dim MyChar$, myPath$, a, i&, k&, Result(), n&
    myPath = ThisWorkbook.Path
    Open myPath & "\Данные.txt" For Input As #1
    Do While Not EOF(1)
        MyChar = MyChar & Input(1, #1)
    Loop
    Close #1
    MyChar = Replace(MyChar, "[", "")
    MyChar = Replace(MyChar, "]", "")
    MyChar = Replace(MyChar, "яю", "")
    a = Split(MyChar, ",")
    ReDim Result((UBound(a) + 1) / 6 - 1, 5)
    For i = 0 To UBound(a) Step 6
        For k = 0 To 5
            Result(n, k) = a(i + k)
        Next
        n = n + 1
    Next
    Cells(2, 1).Resize(UBound(Result) + 1, 6) = Result
End Sub


Данные.txt файл, где будет макрос должны лежать в одной папке.
Макрос запускать при любом чистом листе - результат выведет на активный лист.

зы вопрос знатокам:
В Данные.txt нет, а при чтении почему вначале выводит "яю1" вместо "1"
Интересно, сколько времени потребуется на PQ; у меня на компе этот макрос отработал за 0.37 сек.
Изменено: Михаил Витальевич С. - 29.12.2019 05:41:40
 
Цитата
Katsu написал:
они на поместились в строку, т.е. срезались...
если есть возможность, то  повторите действия в Excel 2010..2016
а лучше воспользуйтесь макросом от Михаил Витальевич С.,
для этого нажмите Alt+F11, затем Insert->Module и в поле вставьте текст кода макроса
закрываете окно VBA и нажимаете Alt+F8. выполняете макрос

в файле разделено
Изменено: artyrH - 29.12.2019 08:20:05
 
Всё получилось, БОЛЬШОЕ спасибо)
artyrH, спасибо, что растолковали, сам бы я долго колупался))
Михаил Витальевич С., спасибо, что написали макрос, работает замечательно)
 
Михаил Витальевич С., это служебные символы кодировки.
Они отсутствуют в кодировках ANSI и UTF-8 без BOM
 
RAN, спасибо!
Страницы: 1
Наверх