主页 / user-553155

Matt Wayne's questions

Martin Hope
Matt Wayne
Asked: 2025-01-21 17:11:04 +0000 UTC
  • 5

有什么问题？程序执行不会产生任何错误，但是当尝试查看加载数据的表时，它只是空的，只有列本身。可能存在什么问题或者程序本身是否需要其他重新编码方法？是的，在将数据加载到表中之前，必须在过程本身中将编码从 utf 8 重新编码为 utf 16。

ALTER PROCEDURE [dbo].[LoadStoresFromXML]
    @XMLFilePath NVARCHAR(MAX) = 'D:\jobLasmart\virtualpos\stores.xml' -- Укажите путь к файлу
AS
BEGIN
    SET NOCOUNT ON;

    -- Переменные для работы с файлами
    DECLARE @XMLData NVARCHAR(MAX);
    DECLARE @FileHandle INT;
    DECLARE @TextStream INT;
    DECLARE @UTF16Data NVARCHAR(MAX);

    -- Читаем содержимое файла в переменную
    EXEC sp_OACreate 'Scripting.FileSystemObject', @FileHandle OUTPUT;
    EXEC sp_OAMethod @FileHandle, 'OpenTextFile', @TextStream OUTPUT, @XMLFilePath, 1, 0; -- 1 = ForReading, 0 = ASCII
    EXEC sp_OAMethod @TextStream, 'ReadAll', @XMLData OUTPUT; -- Читаем весь файл
    EXEC sp_OAMethod @TextStream, 'Close', NULL;
    EXEC sp_OADestroy @TextStream;
    EXEC sp_OADestroy @FileHandle;

    -- Преобразовываем данные в кодировку UTF-16
    -- Datalength увеличивается в 2 раза, так как в UTF-16 каждый символ занимает 2 байта
    SET @UTF16Data = CONVERT(NVARCHAR(MAX), @XMLData COLLATE Latin1_General_BIN);
    
    -- Вставка данных из XML в таблицу Stores
    DECLARE @XML XML = CAST(@UTF16Data AS XML);

    INSERT INTO dbo.Stores (id, open_time, close_time, name, address, phone, headquerter_id, created_date, created_by,
                            last_update_date, last_update_by, flag24hours, lat, lon, minusale, location_id,
                            external_id, show_in_shop, organisation_id, vat_mandatory, manager_user_id, [primary])
    SELECT 
        Store.value('(id)[1]', 'int') AS id,
        Store.value('(open_time)[1]', 'time(7)') AS open_time,
        Store.value('(close_time)[1]', 'time(7)') AS close_time,
        Store.value('(name)[1]', 'nvarchar(255)') AS name,
        Store.value('(address)[1]', 'nvarchar(255)') AS address,
        Store.value('(phone)[1]', 'nvarchar(50)') AS phone,
        Store.value('(headquerter_id)[1]', 'int') AS headquerter_id,
        Store.value('(created_date)[1]', 'datetime') AS created_date,
        Store.value('(created_by)[1]', 'int') AS created_by,
        Store.value('(last_update_date)[1]', 'datetime') AS last_update_date,
        Store.value('(last_update_by)[1]', 'int') AS last_update_by,
        Store.value('(flag24hours)[1]', 'bit') AS flag24hours,
        Store.value('(lat)[1]', 'decimal(9, 6)') AS lat,
        Store.value('(lon)[1]', 'decimal(9, 6)') AS lon,
        Store.value('(minusale)[1]', 'int') AS minusale,
        Store.value('(location_id)[1]', 'int') AS location_id,
        Store.value('(external_id)[1]', 'int') AS external_id,
        Store.value('(show_in_shop)[1]', 'bit') AS show_in_shop,
        Store.value('(organisation_id)[1]', 'int') AS organisation_id,
        Store.value('(vat_mandatory)[1]', 'bit') AS vat_mandatory,
        Store.value('(manager_user_id)[1]', 'int') AS manager_user_id,
        Store.value('(primary)[1]', 'bit') AS [primary]
    FROM @XML.nodes('/Stores/Store') AS Store(Store);
END;```
sql-server
Martin Hope
Matt Wayne
Asked: 2023-09-21 03:22:26 +0000 UTC
  • 5

您需要找到第 259-368 行范围内的 sum 列的总和。为此，您需要将列类型更改为 int 或 float。但它给出了错误 ValueError: Could not conversion string to float: '11\xa0693,70' https://drive.google.com/drive/folders/1Gvg1WdBLdaXmLraoslhHn1PG1P4jqiYH

import numpy as np
import pandas as pd

df = pd.read_csv("data.csv")
#print(test.index)
#print(test.columns)
df['sum'] = df['sum'].astype(float).fillna(0)
general_sum = df.loc[259:368, 'sum'].sum()
print(sum1)
python